﻿using System;
using System.Collections.Generic;
using System.Linq;
using MySql.Data.MySqlClient;
using System.Configuration;
using BoxScore.Objetos;


namespace BoxScore.Model
{
    enum pitchingResult { Gano = 1 , Perdio, SinDecision };

    public class BoxScoreBD
    {
        #region classMethod

        MySqlConnection conexion = null;
        MySqlCommand comando = null;
        private string cadenaConexion = ConfigurationManager.AppSettings["cadenaconexion"];

        public BoxScoreBD()
        {
            conexion = new MySqlConnection(cadenaConexion);
        }

        public bool AbrirConexion()
        {
            bool _result = false;
            try
            {
                conexion.Open();
                _result = true;
            }
            catch (Exception e)
            {
                throw;
            }
            return _result;

        }

        public bool CerrarConexion()
        {
            if (this.conexion != null)
            {
                this.conexion.Close();
                return true;
            }
            return false;
        }


        #endregion classMethod

        #region Ligas

        public List<Liga> getLigas()
        {
            List<Liga> _list = new List<Liga>();
            this.AbrirConexion();
            comando = new MySqlCommand("SELECT id, name, description, picture, logo FROM league");
            comando.Connection = this.conexion;
            comando.CommandType = System.Data.CommandType.Text;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Liga liga = new Liga();
                liga.descripcion = x.GetString(x.GetOrdinal("description"));
                liga.nombre = x.GetString(x.GetOrdinal("name"));
                liga.id = x.GetInt16(x.GetOrdinal("id"));
                liga.logo = x.GetString(x.GetOrdinal("logo"));
                _list.Add(liga);
            }
            this.CerrarConexion();
            return _list;

        }


        public int AgregarLiga(Liga liga)
        {
            int _result = 0;
            this.AbrirConexion();
            comando = new MySqlCommand("INSERT INTO league (name, description, picture, logo) VALUES ('" + liga.nombre + "', '" + liga.descripcion + "', NULL , '" + liga.logo + "'); ");
            comando.Connection = this.conexion;
            comando.CommandType = System.Data.CommandType.Text;
            try
            {
                _result = comando.ExecuteNonQuery();
                comando.CommandText = "SELECT LAST_INSERT_ID()";
                long ultimo = comando.LastInsertedId;
            }
            catch (MySqlException e)
            {
                _result = e.ErrorCode;
            }
            finally
            {
                this.CerrarConexion();
            }


            return _result;
        }

        public Liga getLiga(int id)
        {
            this.AbrirConexion();
            Liga _liga = new Liga();
            comando = new MySqlCommand("SELECT id, name, description, picture, logo FROM league where id = " + id);
            comando.Connection = this.conexion;
            comando.CommandType = System.Data.CommandType.Text;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                _liga = new Liga();
                _liga.descripcion = x.GetString(x.GetOrdinal("description"));
                _liga.nombre = x.GetString(x.GetOrdinal("name"));
                _liga.id = x.GetInt16(x.GetOrdinal("id"));
                _liga.logo = x.GetString(x.GetOrdinal("logo"));
            }
            this.CerrarConexion();
            return _liga;

        }



        #endregion

        #region Torneo

        public List<Torneo> getTorneo(int id)
        {
            List<Torneo> _list = new List<Torneo>();
/*            string sql = "SELECT tournament.id, tournament.name, tournament.description, tournament.start_date, tournament.end_date, tournament.award, ifnull( tournament.picture, '') as picture , ifnull(tournament.logo, '') as logo FROM    (   league_tournament league_tournament";
            sql += " INNER JOIN league league ON (league_tournament.id_league = league.id)) INNER JOIN tournament tournament ON (league_tournament.id_tournament = tournament.id) WHERE (league.id = " + id +")";
*/
            string sql = @"SELECT id, name, description, start_date, end_date, award, picture, logo 
                           FROM tournament
                           WHERE id = ?idtorneo";

            this.AbrirConexion();
            comando = new MySqlCommand(sql);
            comando.Connection = this.conexion;
            comando.CommandType = System.Data.CommandType.Text;

            comando.Parameters.Add(new MySqlParameter("?idtorneo", MySqlDbType.Int32));
            comando.Parameters["?idtorneo"].Value = id;


            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Torneo torneo = new Torneo();
                torneo.name = x.GetString(x.GetOrdinal("name"));
                torneo.description = x.GetString(x.GetOrdinal("description"));
                torneo.start_date = x.GetDateTime(x.GetOrdinal("start_date"));
                torneo.end_date = x.GetDateTime(x.GetOrdinal("end_date"));
                torneo.award = x.GetString(x.GetOrdinal("award"));
                torneo.picture = x.GetString(x.GetOrdinal("picture"));
                torneo.id = x.GetInt16(x.GetOrdinal("id"));
                torneo.logo = x.GetString(x.GetOrdinal("logo"));
                _list.Add(torneo);
            }
            this.CerrarConexion();
            return _list;
        
        }

        public void AgregarTorneo(int liga, string nombre, string descripcion, string start, string end, string premio )
        {
            this.AbrirConexion();
            string sql = "INSERT INTO tournament";
            sql += "(name, description, start_date, end_date, award, picture, logo) ";
            sql += "VALUES ('" + nombre + "', '" + descripcion +"', '" + start +  "', '"+ end+ "', '" + premio + "', NULL, NULL)";

            comando = new MySqlCommand(sql);
            comando.Connection = this.conexion;
            comando.CommandType = System.Data.CommandType.Text;
            int x = comando.ExecuteNonQuery();
            long torneo = comando.LastInsertedId;

            if (comando.LastInsertedId != 0)
            {
                sql = "INSERT INTO league_tournament (id_league, id_tournament) VALUES (" + liga + "," + torneo + ")";
                MySqlCommand comando2 = new MySqlCommand(sql);
                comando2.Connection = this.conexion;
                x = comando2.ExecuteNonQuery();
                long torneo_id = comando2.LastInsertedId;
            }
            this.CerrarConexion();
        }


        public List<Torneo> getTournamentByPlayer(int idplayer)
        {
            List<Torneo> _torneo = new List<Torneo>();

            return _torneo;
        
        }

        public List<Torneo> getTournamentsByLeagues(int idLeague)
        {
            List<Torneo> _list = new List<Torneo>();
            AbrirConexion();
            string sql = @"SELECT league.id as idliga,
                                   tournament.id as idtorneo,
                                   tournament.name as torneo,
                                   tournament.description as descripcion,
                                   tournament.start_date as finicio,
                                   tournament.end_date as ffinal,
                                   tournament.award as premio,
                                   tournament.picture as foto,
                                   tournament.logo as logo
                              FROM    (   league_tournament league_tournament
                                       INNER JOIN
                                          league league
                                       ON (league_tournament.id_league = league.id))
                                   INNER JOIN
                                      tournament tournament
                                   ON (league_tournament.id_tournament = tournament.id)
                             WHERE (league.id = ?idLiga);";
            comando = new MySqlCommand(sql);

            comando.Parameters.Add(new MySqlParameter("?idLiga", MySqlDbType.Int32));
            comando.Parameters["?idLiga"].Value = idLeague;
            comando.Connection = conexion;
            comando.CommandType = System.Data.CommandType.Text;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Torneo torneo = new Torneo();
                torneo.name = x.GetString(x.GetOrdinal("torneo"));
                torneo.id = x.GetInt32(x.GetOrdinal("idtorneo"));
                torneo.award = x.GetString(x.GetOrdinal("premio"));
                torneo.picture = x.GetString(x.GetOrdinal("foto"));
                torneo.logo = x.GetString(x.GetOrdinal("logo"));
                torneo.description = x.GetString(x.GetOrdinal("descripcion"));
                torneo.start_date = x.GetDateTime(x.GetOrdinal("finicio"));
                torneo.end_date = x.GetDateTime(x.GetOrdinal("ffinal"));
                _list.Add(torneo);
                
            }

            CerrarConexion();
            return _list;
        }

        public List<Torneo> getTournamentsByTeam(int idTeam)
        {
            List<Torneo> _list = new List<Torneo>();
            string sql = @"SELECT concat_ws(' / ',league.name, tournament.name) AS torneo,
                                   tournament.id AS idTorneo,
                                   tournament.description AS descripcion,
                                   tournament.start_date AS finicio,
                                   tournament.end_date AS ffinal,
                                   tournament.award AS premio,
                                   tournament.picture AS foto,
                                   tournament.logo AS logo
                              FROM    (   (   (   (   (   (   (   category_cgroup category_cgroup
                                                               INNER JOIN
                                                                  category category
                                                               ON (category_cgroup.id_categoria =
                                                                      category.id))
                                                           INNER JOIN
                                                              cgroup cgroup
                                                           ON (category_cgroup.id_group = cgroup.id))
                                                       INNER JOIN
                                                          cgroup_team cgroup_team
                                                       ON (cgroup_team.id_group = cgroup.id))
                                                   INNER JOIN
                                                      team team
                                                   ON (cgroup_team.id_team = team.id))
                                               INNER JOIN
                                                  tournament_category tournament_category
                                               ON (tournament_category.id_category = category.id))
                                           INNER JOIN
                                              tournament tournament
                                           ON (tournament_category.id_tournament = tournament.id))
                                       INNER JOIN
                                          league_tournament league_tournament
                                       ON (league_tournament.id_tournament = tournament.id))
                                   INNER JOIN
                                      league league
                                   ON (league_tournament.id_league = league.id)
                             WHERE (team.id = ?idEquipo)
                            GROUP BY tournament.id";
            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idEquipo", MySqlDbType.Int32));
            comando.Parameters["?idEquipo"].Value = idTeam;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Torneo torneo = new Torneo();
                torneo.award = x.GetString(x.GetOrdinal("premio"));
                torneo.description = x.GetString(x.GetOrdinal("descripcion"));
                torneo.name = x.GetString(x.GetOrdinal("torneo"));
                torneo.id = x.GetInt32(x.GetOrdinal("idTorneo"));
                torneo.start_date = x.GetDateTime(x.GetOrdinal("finicio"));
                torneo.end_date = x.GetDateTime(x.GetOrdinal("ffinal"));
                if (!x.IsDBNull(x.GetOrdinal("logo") ))
                {
                    torneo.logo = x.GetString(x.GetOrdinal("logo"));   
                }

                if (!x.IsDBNull(x.GetOrdinal("foto")))
                {
                    torneo.picture = x.GetString(x.GetOrdinal("foto"));
                }

                _list.Add(torneo);
            }

            CerrarConexion();
            return _list;
        
        }



        #endregion 

        #region Categoria

        public List<Categoria> getCategoriasPorTorneo(int id)
        {
            List<Categoria> _list = new List<Categoria>();
            this.AbrirConexion();

            string sql = @"SELECT tournament.id as torneo, category.description as categoria, category.id as id
                          FROM    (   tournament_category tournament_category
                                   INNER JOIN
                                      category category
                                   ON (tournament_category.id_category = category.id))
                               INNER JOIN
                                  tournament tournament
                               ON (tournament_category.id_tournament = tournament.id)
                         WHERE (tournament.id = ?id)";


            //string sql = "SELECT category.description, category.id FROM    (   tournament_category tournament_category INNER JOIN tournament tournament";
            //sql += " ON (tournament_category.id_tournament = tournament.id))  INNER JOIN category category ON (tournament_category.id_category = category.id)";

            //if (id != 0)
            //{
            //    sql +=  " WHERE (tournament.id = " + id +" )";
            //}
            //_list.Add( new Categoria{ descripcion="Seleccione...", id=-1});
            comando = new MySqlCommand(sql);
            comando.Connection = this.conexion;

            comando.Parameters.Add(new MySqlParameter("?id", MySqlDbType.Int16));
            comando.Parameters["?id"].Value = id;
            comando.CommandType = System.Data.CommandType.Text;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Categoria categoria = new Categoria();
                categoria.id = x.GetInt16(x.GetOrdinal("id"));
                categoria.descripcion = x.GetString(x.GetOrdinal("categoria"));
                _list.Add(categoria);
            }
            this.CerrarConexion();
            return _list;
        
        }

        public List<Categoria> getCategorias()
        {
            List<Categoria> _list = new List<Categoria>();
            AbrirConexion();
            string sql = @"SELECT id, description 
                            FROM category;";

            comando = new MySqlCommand(sql);
            comando.Connection = conexion;
            comando.CommandType = System.Data.CommandType.Text;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Categoria categoria = new Categoria();
                categoria.id = (int) x["id"];
                categoria.descripcion = x["description"].ToString();
                _list.Add(categoria);
            }
            CerrarConexion();
            return _list;
        }

        public Categoria NuevaCategoria(Categoria categoria)
        {
            this.AbrirConexion();
            string sql = @"INSERT INTO category
                    (description) 
                    VALUES ('" + categoria.descripcion + "');";
            comando = new MySqlCommand(sql);
            comando.Connection = this.conexion;
            comando.CommandType = System.Data.CommandType.Text;
            int x = comando.ExecuteNonQuery();
            categoria.id = (Int32)comando.LastInsertedId;
            this.CerrarConexion();
            return categoria;
        }

        public List<Grupo> getGrupoXCategoria(int idcategoria)
        {
            List<Grupo> _list = new List<Grupo>();

            string sql = @"SELECT cgroup.description, cgroup.id
                      FROM    (   category_cgroup category_cgroup
                               INNER JOIN
                                  cgroup cgroup
                               ON (category_cgroup.id_group = cgroup.id))
                           INNER JOIN
                              category category
                           ON (category_cgroup.id_categoria = category.id)
                       WHERE (category.id = ?idcategoria )";
            this.AbrirConexion();
            //if (idcategoria != 0)
            //{
            //    sql += " WHERE (category.id = " + idcategoria + ")";
            //}
            //_list.Add(new Grupo { descripcion = "Seleccione...", id = -1 });
            comando = new MySqlCommand(sql);
            comando.Connection = this.conexion;
            comando.Parameters.Add( new MySqlParameter("?idcategoria", MySqlDbType.Int32));
            comando.Parameters["?idcategoria"].Value = idcategoria;

            comando.CommandType = System.Data.CommandType.Text;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Grupo grupo = new Grupo();
                grupo.id = x.GetInt16(x.GetOrdinal("id"));
                grupo.descripcion = x.GetString(x.GetOrdinal("description"));
                _list.Add(grupo);
            }
            this.CerrarConexion();
            return _list;

        }


        #endregion

        #region Equipos

        public List<EstadisticaOfensivaEquipo> getEquiposPorTorneoCategoria(int idCategoria, int idGrupo, int idTorneo)
        {
            List<EstadisticaOfensivaEquipo> _list = new List<EstadisticaOfensivaEquipo>();
            //System.Threading.Thread.Sleep(5000);
            string sql = @"SELECT 
			               SUM(offensive_statistic.h) as h,
                           SUM(offensive_statistic.h2) as h2,
                           SUM(offensive_statistic.h3) as h3,
                           SUM(offensive_statistic.hr) as hr,
                           SUM(offensive_statistic.bb) as bb,
                           SUM(offensive_statistic.so) as so,
                           SUM(offensive_statistic.ab) as ab,
                           SUM(offensive_statistic.rbi) as rbi,
                           SUM(offensive_statistic.sb) as sb,
                           SUM(offensive_statistic.run) as r,
                           SUM(offensive_statistic.ibb) as ibb,
                           team.name as name,
                           team.id as id,
  						  (SELECT COUNT(p.ID) FROM PGAME p inner join result on (p.id = result.id_game) WHERE p.id_team_hc = team.id or p.id_team_visitor = team.id) as jugados
                           , (SELECT count(r.id) from result r where (r.draw = 0) and (r.id_team_winner = team.id)) as ganados
                           , (SELECT count(r.id) from result r where (r.draw = 0) and (r.id_team_loser  = team.id) ) as perdidos

                      FROM    (   (   (   (   (   (   (   (   (   category_cgroup category_cgroup
                                                               INNER JOIN
                                                                  category category
                                                               ON (category_cgroup.id_categoria =
                                                                      category.id))
                                                           INNER JOIN
                                                              category_team category_team
                                                           ON (category_team.id_category =
                                                                  category.id))
                                                       INNER JOIN
                                                          team team
                                                       ON (category_team.id_team = team.id))
                                                   INNER JOIN
                                                      player_team player_team
                                                   ON (player_team.id_team = team.id))
                                               INNER JOIN
                                                  player player
                                               ON (player_team.id_player = player.id))
                                           INNER JOIN
                                              offensive_statistic offensive_statistic
                                           ON (offensive_statistic.id_player = player.id))
                                       INNER JOIN
                                          cgroup_team cgroup_team
                                       ON (cgroup_team.id_team = team.id))
                                   INNER JOIN
                                      cgroup cgroup
                                   ON (cgroup_team.id_group = cgroup.id)
                                      AND (category_cgroup.id_group = cgroup.id))
                               INNER JOIN
                                  tournament_category tournament_category
                               ON (tournament_category.id_category = category.id))
                           INNER JOIN
                              tournament tournament
                           ON (tournament_category.id_tournament = tournament.id)";

            sql += " WHERE (tournament.id =" + idTorneo + ")";

            if (idCategoria > 0)
            {
                sql += " AND (category.id =" + idCategoria + ")";
            }

            if (idGrupo > 0)
            {
                sql += " AND  (cgroup.id = " + idGrupo + ")";
            }

            sql += @" GROUP BY team.id";


/*
                     WHERE 
                     (cgroup.id = :idgroup) AND
                     (tournament.id = :idtournament)
                     AND (category.id = :idcategory)
                    GROUP BY team.id";*/

            /*string sql = @"SELECT 
			 SUM(offensive_statistic.h) as h,
       SUM(offensive_statistic.h2) as h2,
       SUM(offensive_statistic.h3) as h3,
       SUM(offensive_statistic.hr) as hr,
       SUM(offensive_statistic.bb) as bb,
       SUM(offensive_statistic.so) as so,
       SUM(offensive_statistic.ab) as ab,
       SUM(offensive_statistic.rbi) as rbi,
       SUM(offensive_statistic.sb) as sb,
       SUM(offensive_statistic.run) as r,
       SUM(offensive_statistic.ibb) as ibb,
       team.name as name,
       team.id as id
  FROM    (   (   (   (   (   (   (   (   cgroup_team cgroup_team
                                       INNER JOIN
                                          cgroup cgroup
                                       ON (cgroup_team.id_group = cgroup.id))
                                   INNER JOIN
                                      team team
                                   ON (cgroup_team.id_team = team.id))
                               INNER JOIN
                                  player_team player_team
                               ON (player_team.id_team = team.id))
                           INNER JOIN
                              player player
                           ON (player_team.id_player = player.id))
                       INNER JOIN
                          offensive_statistic offensive_statistic
                       ON (offensive_statistic.id_player = player.id))
                   INNER JOIN
                      category_cgroup category_cgroup
                   ON (category_cgroup.id_group = cgroup.id))
               INNER JOIN
                  category category
               ON (category_cgroup.id_categoria = category.id))
           INNER JOIN
              tournament_category tournament_category
           ON (tournament_category.id_category = category.id))
       INNER JOIN
          tournament tournament
       ON (tournament_category.id_tournament = tournament.id)";
 
            sql += " WHERE (tournament.id =" + idTorneo + ")";

            if (idCategoria > 0)
            {
                sql += " AND (category.id =" + idCategoria + ")";    
            }
            
            if (idGrupo > 0 )
            {
                sql += " AND  (cgroup.id = " + idGrupo + ")";    
            }
            
            sql += @" GROUP BY team.name
                         , team.id
                         ";*/
            this.AbrirConexion();
            comando = new MySqlCommand(sql);
            comando.Connection = this.conexion;
            comando.CommandType = System.Data.CommandType.Text;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticaOfensivaEquipo estadistica = new EstadisticaOfensivaEquipo();
                estadistica.id = x.GetInt16(x.GetOrdinal("id"));
                estadistica.nombre = x.GetString(x.GetOrdinal("name"));
                estadistica.h =  int.Parse(x["h"].ToString());
                estadistica.h2 = int.Parse(x["h2"].ToString());
                estadistica.h3 = int.Parse(x["h3"].ToString());
                estadistica.hr = int.Parse(x["hr"].ToString());
                estadistica.ibb = int.Parse(x["ibb"].ToString());
                estadistica.ab = int.Parse(x["ab"].ToString());
                estadistica.bb = int.Parse(x["bb"].ToString());
                estadistica.rbi = int.Parse(x["rbi"].ToString());
                estadistica.idequipo = int.Parse(x["id"].ToString());
                estadistica.so = int.Parse(x["so"].ToString());
                estadistica.sb = int.Parse(x["sb"].ToString());
                estadistica.run = int.Parse(x["r"].ToString());
                estadistica.idtorneo = idTorneo;
                estadistica.idcategoria = idCategoria;
                estadistica.juegos = x.GetInt32(x.GetOrdinal("jugados"));
                estadistica.victorias = x.GetInt32(x.GetOrdinal("ganados"));
                estadistica.derrotas = x.GetInt32(x.GetOrdinal("perdidos"));
                estadistica.empates = estadistica.juegos - estadistica.victorias - estadistica.derrotas;
                estadistica.puntos = estadistica.victorias * 3 + estadistica.empates * 1;

                estadistica.average = CalculaAverage(estadistica.h, estadistica.h2, estadistica.h3, estadistica.hr, estadistica.ab); //  ((double)(estadistica.h + estadistica.h2 + estadistica.h3 + estadistica.hr) / (estadistica.ab - estadistica.bb));
                estadistica.slugging = CalculaSluggin(estadistica.h, estadistica.h2, estadistica.h3, estadistica.hr, estadistica.ab); // ((double)((estadistica.h * 1) + (estadistica.h2 * 2) + (estadistica.h3 * 3) + (estadistica.hr * 4)) / (estadistica.ab - estadistica.bb)) ;
                _list.Add(estadistica);
            }

            _list = _list.OrderByDescending(p => p.puntos).ThenByDescending(p=> p.juegos).ToList();
            this.CerrarConexion();
            return _list;

        }

        public List<Equipo> getEquipos()
        {
            List<Equipo> _list = new List<Equipo>();
            string sql = @"SELECT id, name, description, picture, logo 
                            FROM team;";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Equipo e = new Equipo();
                e.description = x.GetString(x.GetOrdinal("description"));
                e.id = x.GetInt32(x.GetOrdinal("id"));
                if (x.IsDBNull(x.GetOrdinal("logo")))
                    e.logo = "";
                else
                    e.logo = x.GetString(x.GetOrdinal("logo"));

                if (x.IsDBNull(x.GetOrdinal("picture")))
                    e.picture = "";
                else
                    e.picture = x.GetString(x.GetOrdinal("picture"));
                e.name = x.GetString(x.GetOrdinal("name"));
                
                _list.Add(e);
            }

            CerrarConexion();
            return _list;
        
        }
        
        public List<Equipo> getEquipo(int id)
        {

            List<Equipo> _list = new List<Equipo>();
            string sql = @"SELECT id, name, description, picture, logo 
                            FROM team where id = ?idequipo;";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idequipo", MySqlDbType.Int32));
            comando.Parameters["?idequipo"].Value = id;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Equipo e = new Equipo();
                e.id = id;
                e.name = x.GetString(x.GetOrdinal("name"));
                e.description = x.GetString(x.GetOrdinal("description"));
                //e.picture = x.GetString(x.GetOrdinal("picture"));
                //e.logo = x.GetString(x.GetOrdinal("logo"));
                _list.Add(e);
                
            }

            CerrarConexion();
            return _list;
            
        
        
        }


        /// <summary>
        /// Dado el Id del equipo se obtienen los detalles del equipo
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Equipo> getDetallesEquipos(int id)
        {

            List<Equipo> _list = new List<Equipo>();
            string sql = @"SELECT id, name, description, picture, logo 
                            FROM team where id = ?idequipo";
            comando = new MySqlCommand(sql);
            comando.Parameters.Add(new MySqlParameter("?idequipo", MySqlDbType.Int32));
            comando.Parameters["?idequipo"].Value = id;
            AbrirConexion();
            comando.Connection = this.conexion;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Equipo equipo = new Equipo();
                equipo.id = int.Parse(x["id"].ToString()); ;
                equipo.logo = x["logo"].ToString();
                equipo.name = x["name"].ToString();
                equipo.description = x["description"].ToString();
                equipo.picture = x["picture"].ToString();
                _list.Add(equipo);
            }
            CerrarConexion();
            return _list;

        }


        public long AgregarEquipo(Equipo equipo)
        {
            string sql = @"INSERT INTO team
                            (name, description, picture, logo) VALUES ";
            sql += "('" + equipo.name + "','" + equipo.description + "', '" + equipo.picture + "', '" + equipo.logo +"')";

            comando = new MySqlCommand(sql);
            comando.Connection = this.conexion;
            comando.CommandType = System.Data.CommandType.Text;
            comando.ExecuteNonQuery();
            long idEquipo = comando.LastInsertedId;
            this.CerrarConexion();

            if (idEquipo != 0)
            {
                return idEquipo;
                
            }
            CerrarConexion();
            return 0;
        
        }

        public List<Equipo> getEquiposPorTorneo(int idliga, int idcategoria)
        {
            List<Equipo> _list = new List<Equipo>();

            return _list;
        }

        public List<Equipo> getEquiposPorTorneo(int idliga, int idcategoria, int id)
        {
            List<Equipo> _list = new List<Equipo>();

            return _list;
        }

        public List<EstadisticasDefensivasEquipo> getEstadisticasDefensivas(int idtorneo, int idequipo, int idcategoria)
        {
            List<EstadisticasDefensivasEquipo> _list = new List<EstadisticasDefensivasEquipo>();
            string sql = @"SELECT COUNT(distinct defensive_statistic.id_game) AS juegos,
                               SUM(defensive_statistic.a) AS asistencias,
                               concat_ws(' ', player.first_name, player.last_name) as PlayerName,
                               player.id AS idPlayer,
                               SUM(DISTINCT defensive_statistic.inning) AS innings,
                               SUM(defensive_statistic.outs) AS outs,
                               SUM(defensive_statistic.e) AS error,
                               SUM(defensive_statistic.dp) AS dobleplays,
                               SUM(defensive_statistic.pb) AS passball
                          FROM    (   (   (   (   (   (   (   (   category_cgroup category_cgroup
                                                               INNER JOIN
                                                                  cgroup cgroup
                                                               ON (category_cgroup.id_group =
                                                                      cgroup.id))
                                                           INNER JOIN
                                                              category category
                                                           ON (category_cgroup.id_categoria =
                                                                  category.id))
                                                       INNER JOIN
                                                          tournament_category tournament_category
                                                       ON (tournament_category.id_category =
                                                              category.id))
                                                   INNER JOIN
                                                      tournament tournament
                                                   ON (tournament_category.id_tournament =
                                                          tournament.id))
                                               INNER JOIN
                                                  cgroup_team cgroup_team
                                               ON (cgroup_team.id_group = cgroup.id))
                                           INNER JOIN
                                              team team
                                           ON (cgroup_team.id_team = team.id))
                                       INNER JOIN
                                          player_team player_team
                                       ON (player_team.id_team = team.id))
                                   INNER JOIN
                                      player player
                                   ON (player_team.id_player = player.id))
                               INNER JOIN
                                  defensive_statistic defensive_statistic
                               ON (defensive_statistic.id_player = player.id)
                         WHERE (category.id = ?idcategoria) AND (team.id = ?idequipo) AND (tournament.id = ?idtorneo)
                        GROUP BY player.id, player.last_name, player.first_name";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idcategoria", MySqlDbType.Int32));
            comando.Parameters.Add(new MySqlParameter("?idtorneo", MySqlDbType.Int32));
            comando.Parameters.Add(new MySqlParameter("?idequipo", MySqlDbType.Int32));

            comando.Parameters["?idcategoria"].Value = idcategoria;
            comando.Parameters["?idtorneo"].Value = idtorneo;
            comando.Parameters["?idequipo"].Value = idequipo;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticasDefensivasEquipo estadistica = new EstadisticasDefensivasEquipo();
                estadistica.g = x.GetInt32(x.GetOrdinal("juegos"));
                estadistica.a = x.GetInt32(x.GetOrdinal("asistencias"));
                estadistica.PlayerName = x.GetString(x.GetOrdinal("PlayerName"));
                estadistica.idPlayer = x.GetInt32(x.GetOrdinal("idPlayer"));
                estadistica.ip = CalculodeInning( x.GetDouble(x.GetOrdinal("innings")));
                estadistica.outs = x.GetInt32(x.GetOrdinal("outs"));
                estadistica.e = x.GetInt32(x.GetOrdinal("error"));
                estadistica.dp = x.GetInt32(x.GetOrdinal("dobleplays"));
                estadistica.pb = x.GetInt32(x.GetOrdinal("passball"));
                estadistica.idtorneo = idtorneo;
                estadistica.idequipo = idequipo;
                estadistica.idcategoria = idcategoria;
                _list.Add(estadistica);
            }
            CerrarConexion();
            return _list;
        }

        public List<EstadisticasPitcheoEquipo> getEstadisticasPitcheo(int idtorneo, int idequipo, int idcategoria)
        {
            List<EstadisticasPitcheoEquipo> _list = new List<EstadisticasPitcheoEquipo>();
            string sql = @"SELECT SUM(pitching_statistic.w) AS ganados,
                               SUM(pitching_statistic.l) AS perdidos,
                               SUM(pitching_statistic.s) AS salvados,
                               SUM(pitching_statistic.cg) AS completo,
                               SUM(pitching_statistic.er) AS carreralimpia,
                               SUM(pitching_statistic.ip) AS inning,
                               SUM(pitching_statistic.h) AS hit,
                               SUM(pitching_statistic.h2) AS doble,
                               SUM(pitching_statistic.h3) AS triple,
                               SUM(pitching_statistic.hr) AS homerun,
                               SUM(pitching_statistic.bb) AS boleto,
                               SUM(pitching_statistic.ibb) AS boletoi,
                               SUM(pitching_statistic.so) AS ponches,
                               SUM(pitching_statistic.run) AS carreras,
                               concat_ws(' ', player.first_name,player.last_name) as playername, 
                               player.id as idplayer
                          FROM    (   (   (   (   (   (   (   (   cgroup_team cgroup_team
                                                               INNER JOIN
                                                                  cgroup cgroup
                                                               ON (cgroup_team.id_group = cgroup.id))
                                                           INNER JOIN
                                                              team team
                                                           ON (cgroup_team.id_team = team.id))
                                                       INNER JOIN
                                                          player_team player_team
                                                       ON (player_team.id_team = team.id))
                                                   INNER JOIN
                                                      player player
                                                   ON (player_team.id_player = player.id))
                                               INNER JOIN
                                                  pitching_statistic pitching_statistic
                                               ON (pitching_statistic.id_player = player.id))
                                           INNER JOIN
                                              category_cgroup category_cgroup
                                           ON (category_cgroup.id_group = cgroup.id))
                                       INNER JOIN
                                          category category
                                       ON (category_cgroup.id_categoria = category.id))
                                   INNER JOIN
                                      tournament_category tournament_category
                                   ON (tournament_category.id_category = category.id))
                               INNER JOIN
                                  tournament tournament
                               ON (tournament_category.id_tournament = tournament.id)
                         WHERE (category.id = ?idcategoria) AND (team.id = ?idequipo) AND (tournament.id = ?idtorneo)
                        GROUP BY player.id";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idcategoria", MySqlDbType.Int32));
            comando.Parameters.Add(new MySqlParameter("?idtorneo", MySqlDbType.Int32));
            comando.Parameters.Add(new MySqlParameter("?idequipo", MySqlDbType.Int32));

            comando.Parameters["?idcategoria"].Value = idcategoria;
            comando.Parameters["?idtorneo"].Value = idtorneo;
            comando.Parameters["?idequipo"].Value = idequipo;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticasPitcheoEquipo estadistica = new EstadisticasPitcheoEquipo();

                estadistica.id_player = x.GetInt32(x.GetOrdinal("idplayer"));
                estadistica.l = x.GetInt32(x.GetOrdinal("perdidos"));
                estadistica.w = x.GetInt32(x.GetOrdinal("ganados"));
                estadistica.savedgame = x.GetInt32(x.GetOrdinal("salvados"));
                estadistica.ip = CalculodeInning( x.GetInt32(x.GetOrdinal("inning")));
                estadistica.h = x.GetInt32(x.GetOrdinal("hit"));
                estadistica.h2 = x.GetInt32(x.GetOrdinal("doble"));
                estadistica.h3 = x.GetInt32(x.GetOrdinal("triple"));

                estadistica.run = x.GetInt32(x.GetOrdinal("carreras"));
                estadistica.erun = x.GetInt32(x.GetOrdinal("carreralimpia"));
                estadistica.hr = x.GetInt32(x.GetOrdinal("homerun"));

                estadistica.bb = x.GetInt32(x.GetOrdinal("boleto"));
                estadistica.ibb = x.GetInt32(x.GetOrdinal("boletoi"));
                estadistica.so = x.GetInt32(x.GetOrdinal("ponches"));
                estadistica.id_player = x.GetInt32(x.GetOrdinal("idplayer"));
                estadistica.nombre = x.GetString(x.GetOrdinal("playername"));



                estadistica.idcategoria = idcategoria;
                estadistica.idtorneo = idtorneo;
                estadistica.idequipo = idequipo;

                _list.Add(estadistica);
            }
            CerrarConexion();
            return _list;
        }


        public List<EstadisticaOfensiva> getTeamRoster(int idTeam)
        {
            List<EstadisticaOfensiva> _list = new List<EstadisticaOfensiva>();

            string sql = @"SELECT concat_ws(' ', player.last_name,player.first_name) as name,
                                   player.id as idPlayer, 
                                   SUM(offensive_statistic.h) as h,
                                   SUM(offensive_statistic.h2) as h2,
                                   SUM(offensive_statistic.h3) as h3,
                                   SUM(offensive_statistic.hr) as hr,
                                   SUM(offensive_statistic.bb) as bb,
                                   SUM(offensive_statistic.so) as so,
                                   SUM(offensive_statistic.ab) as ab,
                                   SUM(offensive_statistic.rbi) as rbi ,
                                   SUM(offensive_statistic.sb) as sb,
                                   SUM(offensive_statistic.run) as run
                              FROM    (   player_team player_team
                                       INNER JOIN
                                          player player
                                       ON (player_team.id_player = player.id))
                                   INNER JOIN
                                      offensive_statistic offensive_statistic
                                   ON (offensive_statistic.id_player = player.id)
                             WHERE (player_team.id_team = ?idteam)
                             group by player.id";
            AbrirConexion();
            comando = new MySqlCommand(sql);
            comando.Parameters.Add(new MySqlParameter("?idteam", MySqlDbType.Int32));
            comando.Parameters["?idteam"].Value = idTeam;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticaOfensiva est = new EstadisticaOfensiva();

                _list.Add(est);
                
            }

            CerrarConexion();
            return _list;
        }


        #endregion

        #region jugadores

        /// <summary>
        /// Calcula las estadisticas Ofensivas del Equipo  en un torneo y categoria donde participo
        /// </summary>
        /// <param name="idtorneo"></param>
        /// <param name="idequipo"></param>
        /// <param name="idcategoria"></param>
        /// <returns></returns>
        public List<EstadisticaOfensiva> getEstadisticasOfensivas(int idtorneo, int idequipo, int idcategoria)
        {
            List<EstadisticaOfensiva> _list = new List<EstadisticaOfensiva>();

            string sql = @"SELECT concat_ws(' ', player.first_name, player.last_name) AS nombre,
                               player.id AS idjugador,
                               SUM(offensive_statistic.h) as hits,
                               SUM(offensive_statistic.h2) as doble,
                               SUM(offensive_statistic.h3) as triple,
                               SUM(offensive_statistic.hr) as homerun,
                               SUM(offensive_statistic.bb) as boleto,
                               SUM(offensive_statistic.so) as ponche,
                               SUM(offensive_statistic.ab) as turno,
                               SUM(offensive_statistic.rbi) as impulsadas,
                               SUM(offensive_statistic.sb) as robadas,
                               SUM(offensive_statistic.run) as anotadas,
                               SUM(offensive_statistic.ibb) as boletoi,
                               team.id AS idequipo
                          FROM    (   (   (   (   (   (   (   (   cgroup_team cgroup_team
                                                               INNER JOIN
                                                                  cgroup cgroup
                                                               ON (cgroup_team.id_group = cgroup.id))
                                                           INNER JOIN
                                                              team team
                                                           ON (cgroup_team.id_team = team.id))
                                                       INNER JOIN
                                                          player_team player_team
                                                       ON (player_team.id_team = team.id))
                                                   INNER JOIN
                                                      player player
                                                   ON (player_team.id_player = player.id))
                                               INNER JOIN
                                                  offensive_statistic offensive_statistic
                                               ON (offensive_statistic.id_player = player.id))
                                           INNER JOIN
                                              category_cgroup category_cgroup
                                           ON (category_cgroup.id_group = cgroup.id))
                                       INNER JOIN
                                          category category
                                       ON (category_cgroup.id_categoria = category.id))
                                   INNER JOIN
                                      tournament_category tournament_category
                                   ON (tournament_category.id_category = category.id))
                               INNER JOIN
                                  tournament tournament
                               ON (tournament_category.id_tournament = tournament.id)
                                              WHERE (tournament_category.id_category = ?idcategoria)
                                              AND (tournament_category.id_tournament = ?idtorneo)
                                              AND (team.id = ?idequipo)
                                        GROUP BY player.id, team.id";

            AbrirConexion();
            comando = new MySqlCommand(sql);
            comando.Parameters.Add(new MySqlParameter("?idcategoria", MySqlDbType.Int32));
            comando.Parameters.Add(new MySqlParameter("?idtorneo", MySqlDbType.Int32));
            comando.Parameters.Add(new MySqlParameter("?idequipo", MySqlDbType.Int32));

            comando.Parameters["?idcategoria"].Value = idcategoria;
            comando.Parameters["?idtorneo"].Value = idtorneo;
            comando.Parameters["?idequipo"].Value = idequipo;
            
            comando.Connection = this.conexion;
            comando.CommandType = System.Data.CommandType.Text;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticaOfensiva estadistica = new EstadisticaOfensiva();
                //estadistica.id = x.GetInt16(x.GetOrdinal("id"));

                estadistica.h = x.GetInt32(x.GetOrdinal("hits"));
                estadistica.h2 = x.GetInt32(x.GetOrdinal("doble"));
                estadistica.h3 = x.GetInt32(x.GetOrdinal("triple"));
                estadistica.hr = x.GetInt32(x.GetOrdinal("homerun"));
                estadistica.bb = x.GetInt32(x.GetOrdinal("boleto"));
                estadistica.ibb = x.GetInt32(x.GetOrdinal("boletoi"));
                estadistica.so = x.GetInt32(x.GetOrdinal("ponche"));
                estadistica.ab = x.GetInt32(x.GetOrdinal("turno"));
                estadistica.rbi = x.GetInt32(x.GetOrdinal("impulsadas"));
                estadistica.sb = x.GetInt32(x.GetOrdinal("robadas"));
                estadistica.run = x.GetInt32(x.GetOrdinal("anotadas"));
                estadistica.nombre = x.GetString(x.GetOrdinal("nombre"));
                estadistica.id_player = x.GetInt32(x.GetOrdinal("idjugador"));
                estadistica.average = this.CalculaAverage(estadistica.h, estadistica.h2, estadistica.h3, estadistica.hr, estadistica.ab);
                estadistica.slugging = this.CalculaSluggin(estadistica.h, estadistica.h2, estadistica.h3, estadistica.hr, estadistica.ab);
                _list.Add(estadistica);
            }
            this.CerrarConexion();
            return _list;
        }

        public List<Jugador> getJugadores()
        {
            List<Jugador> _list = new List<Jugador>();
            string sql = @"SELECT id, concat_ws(' ', first_name, last_name) as name
                                    , ci
                                    , birth_date
                                    , picture
                                    , is_injuried
                                    , is_active
                                    , throwing_arm
                                    , batting 
                                FROM player;";

            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);

            try
            {
                MySqlDataReader x = comando.ExecuteReader();
                while (x.Read())
                {
                    Jugador j = new Jugador();
                    j.id = x.GetInt32(x.GetOrdinal("id"));
                    j.first_name = x.GetString(x.GetOrdinal("name"));
                    j.ci = x.GetString(x.GetOrdinal("ci"));
                    j.birth_date = x.GetString(x.GetOrdinal("birth_date"));
                    j.birth_date = j.birth_date.Substring(0, 10);
                    j.is_injuried = x.GetInt32(x.GetOrdinal("is_injuried"));
                    j.is_active = x.GetInt32(x.GetOrdinal("is_active"));
                    j.throwing_arm_string = x.GetString(x.GetOrdinal("throwing_arm"));
                    j.batting_string = x.GetString(x.GetOrdinal("batting"));
                    if (!x.IsDBNull(x.GetOrdinal("picture")))
                    {
                        j.picture = x.GetString(x.GetOrdinal("picture"));
                    }

                    _list.Add(j);

                }


            }
            catch (Exception e)
            {

                Console.WriteLine(e.Message);
            }
            finally
            {
                CerrarConexion();
            }

            return _list;
        
        }



        public List<Jugador> getJugador(int idliga, int idplayer)
        {

            List<Jugador> _list = new List<Jugador>();
            string sql = @"SELECT id, first_name, last_name, ci, birth_date, picture, is_injuried, is_active, throwing_arm, batting 
                            FROM player where id = ?idjugador;";
            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);
            comando.Parameters.Add ( new MySqlParameter("?idjugador", MySqlDbType.Int32));

            comando.Parameters["?idjugador"].Value = idplayer;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Jugador jugador = new Jugador();
                jugador.id = idplayer;
                jugador.first_name = x.GetString(x.GetOrdinal("first_name"));
                jugador.last_name = x.GetString(x.GetOrdinal("last_name"));
                jugador.batting_string = x.GetString(x.GetOrdinal("batting"));

                if (!x.IsDBNull(x.GetOrdinal("picture")))
                {
                    jugador.picture = x.GetString(x.GetOrdinal("picture"));
                }

                jugador.throwing_arm_string = x.GetString(x.GetOrdinal("throwing_arm"));
                if (x.GetInt32 (x.GetOrdinal("is_active")) == 0)
                {
                    jugador.status = "in";
                    
                }
                jugador.status += "activo / ";

                if (x.GetInt32(x.GetOrdinal("is_injuried")) == 0)
                {
                    jugador.status += "Activo";

                }
                else
                {
                    jugador.status += "Lesionado";
                }
                _list.Add(jugador);
            }
            CerrarConexion();
            return _list;
        
        }

        public List<Jugador> getPlayerByTeamAndGame(int idteam, int idgame)
        {
            List<Jugador> _jugador = new List<Jugador>();

            Jugador jugador = new Jugador();
            jugador.id = 1;
            jugador.first_name = "Armando E.";
            jugador.last_name = "Iglesias A.";
            _jugador.Add(jugador);

            Jugador jugador2 = new Jugador();
            jugador2.id =2;
            jugador2.first_name = "Armando";
            jugador2.last_name = "Iglesias";
            _jugador.Add(jugador2);


            //CerrarConexion();

            return _jugador;
        
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="idTorneo"></param>
        /// <param name="idRenglon">
        /// 1 para AVG, 2 para HR, 3 para RBI
        /// </param>
        /// <returns></returns>
        public List<EstadisticaOfensiva> getOffensiveStaticsByTournament(int idTorneo, int idRenglon )
        {
            List<EstadisticaOfensiva> _list = new List<EstadisticaOfensiva>();

            string sql = @"SELECT sum(offensive_statistic.so) as ponche,
                               sum(offensive_statistic.h) as hits,
                               sum(offensive_statistic.h2) as doble,
                               sum(offensive_statistic.hr) as homerun,
                               sum(offensive_statistic.h3) as triple,
                               sum(offensive_statistic.bb) as boleto,
                               sum(offensive_statistic.rbi) as rbi,
                               sum(offensive_statistic.ab) as turnos,
                               sum(offensive_statistic.sb) as robadas,
                               sum(offensive_statistic.run) as anotadas,
                               sum(offensive_statistic.ibb) as boletoi,
                               offensive_statistic.id_game as idjuego,
                                player.id as idPlayer,
                                concat_ws(' ', player.first_name, player.last_name) as nombre
                          FROM    (   (   (   game_tournament game_tournament
                                           INNER JOIN
                                              tournament tournament
                                           ON (game_tournament.id_tournament = tournament.id))
                                       INNER JOIN
                                          pgame pgame
                                       ON (game_tournament.id_game = pgame.id))
                                   INNER JOIN
                                      offensive_statistic offensive_statistic
                                   ON (offensive_statistic.id_game = pgame.id))
                               INNER JOIN
                                  player player
                               ON (offensive_statistic.id_player = player.id)
                         WHERE (tournament.id = ?idTorneo)
                         GROUP BY player.id";

            /*string sql = @"SELECT offensive_statistic.so as ponche,
                               offensive_statistic.h as hits,
                               offensive_statistic.h2 as doble,
                               offensive_statistic.hr as homerun,
                               offensive_statistic.h3 as triple,
                               offensive_statistic.bb as boleto,
                               offensive_statistic.rbi as rbi,
                               offensive_statistic.ab as turnos,
                               offensive_statistic.sb as robadas,
                               offensive_statistic.run as anotadas,
                               offensive_statistic.ibb as boletoi,
                               offensive_statistic.id_game as idjuego,
                                player.id as idPlayer,
                                concat_ws(' ', player.first_name, player.last_name) as nombre
                          FROM    (   (   (   game_tournament game_tournament
                                           INNER JOIN
                                              tournament tournament
                                           ON (game_tournament.id_tournament = tournament.id))
                                       INNER JOIN
                                          pgame pgame
                                       ON (game_tournament.id_game = pgame.id))
                                   INNER JOIN
                                      offensive_statistic offensive_statistic
                                   ON (offensive_statistic.id_game = pgame.id))
                               INNER JOIN
                                  player player
                               ON (offensive_statistic.id_player = player.id)
                         WHERE (tournament.id = ?idTorneo)";
             */

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idTorneo", MySqlDbType.Int32));

            comando.Parameters["?idTorneo"].Value = idTorneo;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticaOfensiva est = new EstadisticaOfensiva();
                est.ab = x.GetInt32(x.GetOrdinal("turnos"));
                est.so = x.GetInt32(x.GetOrdinal("ponche"));
                est.h = x.GetInt32(x.GetOrdinal("hits"));
                est.h2 = x.GetInt32(x.GetOrdinal("doble"));
                est.h3 = x.GetInt32(x.GetOrdinal("triple"));
                est.hr = x.GetInt32(x.GetOrdinal("homerun"));
                est.bb = x.GetInt32(x.GetOrdinal("boleto"));
                est.ibb = x.GetInt32(x.GetOrdinal("boletoi"));
                est.rbi = x.GetInt32(x.GetOrdinal("rbi"));
                est.sb = x.GetInt32(x.GetOrdinal("robadas"));
                est.run = x.GetInt32(x.GetOrdinal("anotadas"));
                est.id_game = x.GetInt32(x.GetOrdinal("idjuego"));
                est.id_player = x.GetInt32(x.GetOrdinal("idPlayer"));
                est.nombre = x.GetString(x.GetOrdinal("nombre"));
                est.average = this.CalculaAverage(est.h, est.h2, est.h3, est.hr, est.ab);
                _list.Add(est);

            }

            switch (idRenglon)
            {
                case 2:
                    _list = _list.OrderByDescending(p => p.hr).ToList();
                    break;
                case 3:
                    _list = _list.OrderByDescending(p => p.rbi).ToList();
                    break;
                case 4:
                    _list = _list.OrderByDescending(p => p.run).ToList();
                    break;
                case 5:
                    _list = _list.OrderByDescending(p => p.h).ToList();
                    break;
                case 6:
                    _list = _list.OrderByDescending(p => p.h2).ToList();
                    break;
                case 7:
                    _list = _list.OrderByDescending(p => p.h3).ToList();
                    break;
                case 8:
                    _list = _list.OrderByDescending(p => p.bb).ToList();
                    break;
                case 9:
                    _list = _list.OrderByDescending(p => p.ibb).ToList();
                    break;
                case 10:
                    _list = _list.OrderByDescending(p => p.so).ToList();
                    break;
                case 11:
                    _list = _list.OrderByDescending(p => p.sb).ToList();
                    break;
                case 12:
                    _list = _list.OrderByDescending(p => p.slugging).ToList();
                    break;
                default:
                    _list = _list.OrderByDescending(p => p.average).ToList();
                    break;
            }

            CerrarConexion();
            return _list;
    
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="idTorneo"></param>
        /// <param name="idRenglon">
        /// 1 para AVG, 2 para HR, 3 para RBI
        /// </param>
        /// <returns></returns>
        public List<EstadisticaOfensiva> getOffensiveLeadersByTeam(int idTeam, int idRenglon)
        {
            List<EstadisticaOfensiva> _list = new List<EstadisticaOfensiva>();
            string sql = @"SELECT sum(offensive_statistic.so) AS ponche,
                                   sum(offensive_statistic.h) AS hits,
                                   sum(offensive_statistic.h2) AS doble,
                                   sum(offensive_statistic.hr) AS homerun,
                                   sum(offensive_statistic.h3) AS triple,
                                   sum(offensive_statistic.bb) AS boleto,
                                   sum(offensive_statistic.rbi) AS rbi,
                                   sum(offensive_statistic.ab) AS turnos,
                                   sum(offensive_statistic.sb) AS robadas,
                                   sum(offensive_statistic.run) AS anotadas,
                                   sum(offensive_statistic.ibb) AS boletoi,
                                   player.id AS idPlayer,
                                   concat_ws(' ', player.first_name, player.last_name) AS nombre
                              FROM    (   (   offensive_statistic offensive_statistic
                                           INNER JOIN
                                              player player
                                           ON (offensive_statistic.id_player = player.id))
                                       INNER JOIN
                                          player_team player_team
                                       ON (player_team.id_player = player.id))
                                   INNER JOIN
                                      team team
                                   ON (player_team.id_team = team.id)
                             WHERE (player_team.id_team = ?idTeam)
                             group by player.id";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idTeam", MySqlDbType.Int32));

            comando.Parameters["?idTeam"].Value = idTeam;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticaOfensiva est = new EstadisticaOfensiva();
                est.ab = x.GetInt32(x.GetOrdinal("turnos"));
                est.so = x.GetInt32(x.GetOrdinal("ponche"));
                est.h = x.GetInt32(x.GetOrdinal("hits"));
                est.h2 = x.GetInt32(x.GetOrdinal("doble"));
                est.h3 = x.GetInt32(x.GetOrdinal("triple"));
                est.hr = x.GetInt32(x.GetOrdinal("homerun"));
                est.bb = x.GetInt32(x.GetOrdinal("boleto"));
                est.ibb = x.GetInt32(x.GetOrdinal("boletoi"));
                est.rbi = x.GetInt32(x.GetOrdinal("rbi"));
                est.sb = x.GetInt32(x.GetOrdinal("robadas"));
                est.run = x.GetInt32(x.GetOrdinal("anotadas"));
                //est.id_game = x.GetInt32(x.GetOrdinal("idjuego"));
                est.id_player = x.GetInt32(x.GetOrdinal("idPlayer"));
                est.nombre = x.GetString(x.GetOrdinal("nombre"));
                est.average = this.CalculaAverage(est.h, est.h2, est.h3, est.hr, est.ab);
                est.slugging = this.CalculaSluggin(est.h, est.h2, est.h3, est.hr, est.ab);
                _list.Add(est);

            }

            switch (idRenglon)
            {
                case 2:
                    _list = _list.OrderByDescending(p => p.hr).ToList();
                    break;
                case 3:
                    _list = _list.OrderByDescending(p => p.rbi).ToList();
                    break;
                case 4:
                    _list = _list.OrderByDescending(p => p.run).ToList();
                    break;
                case 5:
                    _list = _list.OrderByDescending(p => p.h).ToList();
                    break;
                case 6:
                    _list = _list.OrderByDescending(p => p.h2).ToList();
                    break;
                case 7:
                    _list = _list.OrderByDescending(p => p.h3).ToList();
                    break;
                case 8:
                    _list = _list.OrderByDescending(p => p.bb).ToList();
                    break;
                case 9:
                    _list = _list.OrderByDescending(p => p.ibb).ToList();
                    break;
                case 10:
                    _list = _list.OrderByDescending(p => p.so).ToList();
                    break;
                case 11:
                    _list = _list.OrderByDescending(p => p.sb).ToList();
                    break;
                case 12:
                    _list = _list.OrderByDescending(p => p.slugging).ToList();
                    break;
                default:
                    _list = _list.OrderByDescending(p => p.average).ToList();
                    break;
            }


            CerrarConexion();
            return _list;

        }



        public List<EstadisticaOfensiva> getOffensivaStaticsByTournament(int idtournament, int idplayer)
        {
            List<EstadisticaOfensiva> _list = new List<EstadisticaOfensiva>();

            string sql = @"SELECT offensive_statistic.so as ponche,
                               offensive_statistic.h as hits,
                               offensive_statistic.h2 as doble,
                               offensive_statistic.hr as homerun,
                               offensive_statistic.h3 as triple,
                               offensive_statistic.bb as boleto,
                               offensive_statistic.rbi as rbi,
                               offensive_statistic.ab as turnos,
                               offensive_statistic.sb as robadas,
                               offensive_statistic.run as anotadas,
                               offensive_statistic.ibb as boletoi,
                               offensive_statistic.id_game as idjuego
                          FROM    (   (   (   game_tournament game_tournament
                                           INNER JOIN
                                              tournament tournament
                                           ON (game_tournament.id_tournament = tournament.id))
                                       INNER JOIN
                                          pgame pgame
                                       ON (game_tournament.id_game = pgame.id))
                                   INNER JOIN
                                      offensive_statistic offensive_statistic
                                   ON (offensive_statistic.id_game = pgame.id))
                               INNER JOIN
                                  player player
                               ON (offensive_statistic.id_player = player.id)
                         WHERE (player.id = ?idplayer)
                         AND (tournament.id = ?idTorneo)";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idplayer", MySqlDbType.Int32));
            comando.Parameters.Add(new MySqlParameter("?idTorneo", MySqlDbType.Int32));

            comando.Parameters["?idplayer"].Value = idplayer;
            comando.Parameters["?idTorneo"].Value = idtournament;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticaOfensiva est = new EstadisticaOfensiva();
                est.ab = x.GetInt32(x.GetOrdinal("turnos"));
                est.so = x.GetInt32(x.GetOrdinal("ponche"));
                est.h = x.GetInt32(x.GetOrdinal("hits"));
                est.h2 = x.GetInt32(x.GetOrdinal("doble"));
                est.h3 = x.GetInt32(x.GetOrdinal("triple"));
                est.hr = x.GetInt32(x.GetOrdinal("homerun"));
                est.bb = x.GetInt32(x.GetOrdinal("boleto"));
                est.ibb = x.GetInt32(x.GetOrdinal("boletoi"));
                est.rbi = x.GetInt32(x.GetOrdinal("rbi"));
                est.sb = x.GetInt32(x.GetOrdinal("robadas"));
                est.run = x.GetInt32(x.GetOrdinal("anotadas"));
                est.id_game = x.GetInt32(x.GetOrdinal("idjuego"));
                est.id_player = idplayer;
                est.average = this.CalculaAverage(est.h, est.h2, est.h3, est.hr, est.ab);
                est.slugging = this.CalculaSluggin(est.h, est.h2, est.h3, est.hr, est.ab);
                _list.Add(est);

            }

            CerrarConexion();
            return _list;
        }

        public List<EstadisticaDefensiva> getDefensiveStaticsByTournament(int idtournament, int idplayer)
        {
            List<EstadisticaDefensiva> _list = new List<EstadisticaDefensiva>();

            string sql = @"SELECT defensive_statistic.outs AS outs,
                               defensive_statistic.a AS asistencia,
                               defensive_statistic.e AS error,
                               defensive_statistic.dp AS dobleplay,
                               defensive_statistic.pb AS passball,
                               dposition.description AS posicion,
                               pgame.id AS idJuego,
                               defensive_statistic.inning AS innings
                          FROM    (   (   (   (   game_tournament game_tournament
                                               INNER JOIN
                                                  tournament tournament
                                               ON (game_tournament.id_tournament = tournament.id))
                                           INNER JOIN
                                              pgame pgame
                                           ON (game_tournament.id_game = pgame.id))
                                       INNER JOIN
                                          defensive_statistic defensive_statistic
                                       ON (defensive_statistic.id_game = pgame.id))
                                   INNER JOIN
                                      player player
                                   ON (defensive_statistic.id_player = player.id))
                               INNER JOIN
                                  dposition dposition
                               ON (defensive_statistic.id_position = dposition.id)
                         WHERE (player.id = ?idplayer)
                         AND (tournament.id = ?idTorneo)
                        GROUP BY dposition.description";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idplayer", MySqlDbType.Int32));
            comando.Parameters.Add(new MySqlParameter("?idTorneo", MySqlDbType.Int32));

            comando.Parameters["?idplayer"].Value = idplayer;
            comando.Parameters["?idTorneo"].Value = idtournament;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticaDefensiva est = new EstadisticaDefensiva();
                est.a = x.GetInt32(x.GetOrdinal("asistencia"));
                est.outs = x.GetInt32(x.GetOrdinal("outs"));
                est.e = x.GetInt32(x.GetOrdinal("error"));
                est.dp = x.GetInt32(x.GetOrdinal("dobleplay"));
                est.pb = x.GetInt32(x.GetOrdinal("passball"));
                est.positionString = x.GetString(x.GetOrdinal("posicion"));
                est.ip = CalculodeInning( x.GetDouble(x.GetOrdinal("innings")));
                est.id = idplayer;
                est.g = x.GetInt32(x.GetOrdinal("idJuego"));
                _list.Add(est);
                
            }

            CerrarConexion();
            return _list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="idTorneo"></param>
        /// id Torneo a consultar
        /// <param name="idRubro"></param>
        /// 1 para victoria, 2 para ponches, 3 para ERA
        /// <returns></returns>
        public List<EstadisticasPitcheo> getPitchingLeadersByTournament(int idTorneo, int idRubro)
        {
            List<EstadisticasPitcheo> _list = new List<EstadisticasPitcheo>();
            string sql = @"SELECT  SUM(pitching_statistic.w) AS victorias
                             , SUM(pitching_statistic.l) AS derrotas
                             , SUM(pitching_statistic.s) AS salvados
                             , SUM(pitching_statistic.cg) AS completos
                             , SUM(pitching_statistic.er) AS carreraslimpias
                             , SUM(pitching_statistic.ip) AS innings
                             , SUM(pitching_statistic.h) AS hits
                             , SUM(pitching_statistic.h2) AS dobles
                             , SUM(pitching_statistic.h3) AS triples
                             , SUM(pitching_statistic.hr) AS homeruns
                             , SUM(pitching_statistic.bb) AS boletos
                             , SUM(pitching_statistic.ibb) AS boletosi
                             , SUM(pitching_statistic.so) AS ponches
                             , SUM(pitching_statistic.run) AS carreras
                             , pitching_statistic.id_player AS idPlayer
                             , pitching_statistic.id_pitchingType AS tipoPitcher
                            FROM
                             ((pitching_statistic pitching_statistic
                             INNER JOIN
                             pgame pgame
                             ON (pitching_statistic.id_game = pgame.id))
                             INNER JOIN
                             game_tournament game_tournament
                             ON (game_tournament.id_game = pgame.id))
                             INNER JOIN
                             tournament tournament
                             ON (game_tournament.id_tournament = tournament.id)
                            WHERE (tournament.id = ?idTorneo)
                            GROUP BY pitching_statistic.id_player";

            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idTorneo", MySqlDbType.Int32));
            comando.Parameters["?idTorneo"].Value = idTorneo;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticasPitcheo est = new EstadisticasPitcheo();
                est.w = x.GetInt32(x.GetOrdinal("victorias"));
                est.l = x.GetInt32(x.GetOrdinal("derrotas"));
                est.savedgame = x.GetInt32(x.GetOrdinal("salvados"));

                est.cg = x.GetInt32(x.GetOrdinal("completos"));
                est.erun = x.GetInt32(x.GetOrdinal("carreraslimpias"));
                est.ip = CalculodeInning(x.GetDouble(x.GetOrdinal("innings")));
                est.h = x.GetInt32(x.GetOrdinal("hits"));
                est.h2 = x.GetInt32(x.GetOrdinal("dobles"));
                est.h3 = x.GetInt32(x.GetOrdinal("triples"));
                est.hr = x.GetInt32(x.GetOrdinal("homeruns"));
                est.bb = x.GetInt32(x.GetOrdinal("boletos"));
                est.ibb = x.GetInt32(x.GetOrdinal("boletosi"));
                est.so = x.GetInt32(x.GetOrdinal("ponches"));
                est.run = x.GetInt32(x.GetOrdinal("carreras"));
                est.id_player = x.GetInt32(x.GetOrdinal("idPlayer"));
                est.positionString = x.GetString(x.GetOrdinal("tipoPitcher"));
                est.average = this.CalculaERA(est.erun, est.ip, 7);
                _list.Add(est);
            }

            switch (idRubro)
            {
                case 2:
                    _list = _list.OrderByDescending(p => p.so).ToList();
                    break;
                case 3:
                    _list = _list.OrderBy(p => p.average).ToList();
                    break;
                case 4:
                    _list = _list.OrderBy(p => p.l).ToList();
                    break;
                case 5:
                    _list = _list.OrderBy(p => p.savedgame).ToList();
                    break;
                case 6:
                    _list = _list.OrderBy(p => p.cg).ToList();
                    break;
                case 7:
                    _list = _list.OrderBy(p => p.run).ToList();
                    break;
                case 8:
                    _list = _list.OrderBy(p => p.erun).ToList();
                    break;
                case 9:
                    _list = _list.OrderBy(p => p.ip).ToList();
                    break;
                case 10:
                    _list = _list.OrderBy(p => p.h).ToList();
                    break;
                case 11:
                    _list = _list.OrderBy(p => p.h2).ToList();
                    break;
                case 12:
                    _list = _list.OrderBy(p => p.h3).ToList();
                    break;
                case 13:
                    _list = _list.OrderBy(p => p.hr).ToList();
                    break;
                case 14:
                    _list = _list.OrderBy(p => p.bb).ToList();
                    break;
                case 15:
                    _list = _list.OrderBy(p => p.ibb).ToList();
                    break;
                default:
                    _list = _list.OrderByDescending(p => p.w).ToList();
                    break;
            }
            CerrarConexion();
            return _list;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="idTorneo"></param>
        /// id Torneo a consultar
        /// <param name="idRubro"></param>
        /// 1 para victoria, 2 para ponches, 3 para ERA
        /// <returns></returns>
        public List<EstadisticasPitcheo> getPitchingLeadersByTeam(int idTeam, int idRubro)
        {
            List<EstadisticasPitcheo> _list = new List<EstadisticasPitcheo>();
            string sql = @"SELECT  SUM(pitching_statistic.w) AS victorias
                                 , SUM(pitching_statistic.l) AS derrotas
                                 , SUM(pitching_statistic.s) AS salvados
                                 , SUM(pitching_statistic.cg) AS completos
                                 , SUM(pitching_statistic.er) AS carreraslimpias
                                 , SUM(pitching_statistic.ip) AS innings
                                 , SUM(pitching_statistic.h) AS hits
                                 , SUM(pitching_statistic.h2) AS dobles
                                 , SUM(pitching_statistic.h3) AS triples
                                 , SUM(pitching_statistic.hr) AS homeruns
                                 , SUM(pitching_statistic.bb) AS boletos
                                 , SUM(pitching_statistic.ibb) AS boletosi
                                 , SUM(pitching_statistic.so) AS ponches
                                 , SUM(pitching_statistic.run) AS carreras
                                 , pitching_statistic.id_player AS idPlayer
                                 , pitching_statistic.id_pitchingType AS tipoPitcher
                              FROM   pitching_statistic pitching_statistic
                                 		INNER JOIN
                                         player player
                                 	    ON (pitching_statistic.id_player = player.id)
                                    	INNER JOIN
                                       player_team player_team
                                    		ON (player_team.id_player = player.id)
                            			INNER JOIN
                               			team team
                            				ON (player_team.id_team = team.id)
						 			    INNER JOIN pitchingtype pitchingtype
						 				    ON (pitchingtype.id = pitching_statistic.id_pitchingType)
                             WHERE (player_team.id_team = ?idTeam)
															GROUP BY pitching_statistic.id_player, pitching_statistic.id_pitchingType";

            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idTeam", MySqlDbType.Int32));
            comando.Parameters["?idTeam"].Value = idTeam;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticasPitcheo est = new EstadisticasPitcheo();
                est.w = x.GetInt32(x.GetOrdinal("victorias"));
                est.l = x.GetInt32(x.GetOrdinal("derrotas"));
                est.savedgame = x.GetInt32(x.GetOrdinal("salvados"));

                est.cg = x.GetInt32(x.GetOrdinal("completos"));
                est.erun = x.GetInt32(x.GetOrdinal("carreraslimpias"));
                est.ip = CalculodeInning(x.GetDouble(x.GetOrdinal("innings")));
                est.h = x.GetInt32(x.GetOrdinal("hits"));
                est.h2 = x.GetInt32(x.GetOrdinal("dobles"));
                est.h3 = x.GetInt32(x.GetOrdinal("triples"));
                est.hr = x.GetInt32(x.GetOrdinal("homeruns"));
                est.bb = x.GetInt32(x.GetOrdinal("boletos"));
                est.ibb = x.GetInt32(x.GetOrdinal("boletosi"));
                est.so = x.GetInt32(x.GetOrdinal("ponches"));
                est.run = x.GetInt32(x.GetOrdinal("carreras"));
                est.id_player = x.GetInt32(x.GetOrdinal("idPlayer"));
                est.positionString = x.GetString(x.GetOrdinal("tipoPitcher"));
                est.average = this.CalculaERA(est.erun, est.ip, 7);
                _list.Add(est);
            }

            switch (idRubro)
            {
                case 2:
                    _list = _list.OrderByDescending(p => p.so).ToList();
                    break;
                case 3:
                    _list = _list.OrderBy(p => p.average).ToList();
                    break;
                case 4:
                    _list = _list.OrderBy(p => p.l).ToList();
                    break;
                case 5:
                    _list = _list.OrderBy(p => p.savedgame).ToList();
                    break;
                case 6:
                    _list = _list.OrderBy(p => p.cg).ToList();
                    break;
                case 7:
                    _list = _list.OrderBy(p => p.run).ToList();
                    break;
                case 8:
                    _list = _list.OrderBy(p => p.erun).ToList();
                    break;
                case 9:
                    _list = _list.OrderBy(p => p.ip).ToList();
                    break;
                case 10:
                    _list = _list.OrderBy(p => p.h).ToList();
                    break;
                case 11:
                    _list = _list.OrderBy(p => p.h2).ToList();
                    break;
                case 12:
                    _list = _list.OrderBy(p => p.h3).ToList();
                    break;
                case 13:
                    _list = _list.OrderBy(p => p.hr).ToList();
                    break;
                case 14:
                    _list = _list.OrderBy(p => p.bb).ToList();
                    break;
                case 15:
                    _list = _list.OrderBy(p => p.ibb).ToList();
                    break;
                default:
                    _list = _list.OrderByDescending(p => p.w).ToList();
                    break;
            }
            CerrarConexion();
            return _list;
        }


        public List<EstadisticaDefensiva> getDefensiveLeaderByTournament(int idTorneo, int idPosicion, int idRubro )
        {
            List<EstadisticaDefensiva> _list = new List<EstadisticaDefensiva>();
            string sql = @"SELECT  SUM(defensive_statistic.inning) as innings
                             , SUM(defensive_statistic.outs) as o
                             , SUM(defensive_statistic.a) as a
                             , SUM(defensive_statistic.e) as e
                             , SUM(defensive_statistic.dp) as dp
                             , SUM(defensive_statistic.pb) as pb
                             , COUNT(defensive_statistic.id) as g
                             , concat_ws(' '  , player.first_name, player.last_name ) as PlayerName
                             , player.id as idPlayer
                            FROM
                             (((game_tournament game_tournament
                             INNER JOIN
                             tournament tournament
                             ON (game_tournament.id_tournament = tournament.id))
                             INNER JOIN
                             pgame pgame
                             ON (game_tournament.id_game = pgame.id))
                             INNER JOIN
                             defensive_statistic defensive_statistic
                             ON (defensive_statistic.id_game = pgame.id))
                             INNER JOIN
                             player player
                             ON (defensive_statistic.id_player = player.id)
                            WHERE (defensive_statistic.id_position = ?idPosition)
                             AND (tournament.id = ?idTorneo)
                            GROUP BY player.id";

            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idTorneo", MySqlDbType.Int32));
            comando.Parameters.Add(new MySqlParameter("?idPosition", MySqlDbType.Int32));
            comando.Parameters["?idPosition"].Value = idPosicion;
            comando.Parameters["?idTorneo"].Value = idTorneo;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticaDefensiva est = new EstadisticaDefensiva();
                est.a = x.GetInt32(x.GetOrdinal("a"));
                est.dp = x.GetInt32(x.GetOrdinal("dp"));
                est.e = x.GetInt32(x.GetOrdinal("e"));
                est.g = x.GetInt32(x.GetOrdinal("g"));
                est.idposition = idPosicion;
                est.idPlayer = x.GetInt32(x.GetOrdinal("idPlayer"));
                est.ip = x.GetDouble(x.GetOrdinal("innings"));
                est.outs = x.GetInt32(x.GetOrdinal("o"));
                est.pb = x.GetInt32(x.GetOrdinal("pb"));
                est.PlayerName = x.GetString(x.GetOrdinal("PlayerName"));
                est.pFildeo = this.CalculodePorcentajeFildeo(est.outs, est.e, est.a);
                _list.Add(est);
            }

            switch (idRubro)
	        {
                case 2:
                    _list = _list.OrderBy(p => p.a).ToList();
                    break;
                case 3:
                    _list = _list.OrderBy(p => p.e).ToList();
                    break;
                case 4:
                    _list = _list.OrderBy(p => p.outs).ToList();
                    break;
                case 5:
                    _list = _list.OrderBy(p => p.ip).ToList();
                    break;
                case 6:
                    _list = _list.OrderBy(p => p.dp).ToList();
                    break;
                case 7:
                    _list = _list.OrderBy(p => p.pb).ToList();
                    break;
		        default:
                    _list = _list.OrderBy(p => p.pFildeo).ToList();
                    break;
	        }
            CerrarConexion();
            return _list;
        }

        public List<EstadisticasPitcheo> getPitchingStaticsByTournament(int idtournament, int idplayer)
        {
            List<EstadisticasPitcheo> _list = new List<EstadisticasPitcheo>();
            string sql = @"SELECT pitchingtype.description as pitcher,
                                   pitching_statistic.w as victoria,
                                   pitching_statistic.l as derrota ,
                                   pitching_statistic.s as salvado,
                                   pitching_statistic.cg as completo,
                                   pitching_statistic.er as carreralimpia,
                                   pitching_statistic.ip as innings,
                                   pitching_statistic.h as hits,
                                   pitching_statistic.h2 as doble,
                                   pitching_statistic.h3 as triple,
                                   pitching_statistic.bb as boleto,
                                   pitching_statistic.hr as homerun,
                                   pitching_statistic.ibb as boletoi,
                                   pitching_statistic.so as ponche,
                                   pitching_statistic.run as carrera,
                                   pgame.id as idJuego
                              FROM    (   (   (   (   game_tournament game_tournament
                                                   INNER JOIN
                                                      tournament tournament
                                                   ON (game_tournament.id_tournament = tournament.id))
                                               INNER JOIN
                                                  pgame pgame
                                               ON (game_tournament.id_game = pgame.id))
                                           INNER JOIN
                                              pitching_statistic pitching_statistic
                                           ON (pitching_statistic.id_game = pgame.id))
                                       INNER JOIN
                                          pitchingtype pitchingtype
                                       ON (pitching_statistic.id_pitchingType = pitchingtype.id))
                                   INNER JOIN
                                      player player
                                   ON (pitching_statistic.id_player = player.id)
                             WHERE (player.id = ?idplayer)
                             AND (tournament.id = ?idTorneo)
                             order by pgame.id";


            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idplayer", MySqlDbType.Int32));
            comando.Parameters.Add(new MySqlParameter("?idTorneo", MySqlDbType.Int32));

            comando.Parameters["?idplayer"].Value = idplayer;
            comando.Parameters["?idTorneo"].Value = idtournament;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticasPitcheo est = new EstadisticasPitcheo();
                est.cg = x.GetInt32(x.GetOrdinal("completo"));
                est.erun = x.GetInt32(x.GetOrdinal("carreralimpia"));
                est.ip = CalculodeInning( x.GetDouble(x.GetOrdinal("innings")));
                est.h = x.GetInt32(x.GetOrdinal("hits"));
                est.h2 = x.GetInt32(x.GetOrdinal("doble"));
                est.h3 = x.GetInt32(x.GetOrdinal("triple"));
                est.hr = x.GetInt32(x.GetOrdinal("homerun"));
                est.bb = x.GetInt32(x.GetOrdinal("boleto"));
                est.ibb = x.GetInt32(x.GetOrdinal("boletoi"));
                est.so = x.GetInt32(x.GetOrdinal("ponche"));
                est.run = x.GetInt32(x.GetOrdinal("carrera"));
                est.id_game = x.GetInt32(x.GetOrdinal("idJuego"));
                est.id_player = idplayer;
                est.positionString = x.GetString(x.GetOrdinal("pitcher"));

                est.w = x.GetInt32(x.GetOrdinal("victoria"));
                est.l = x.GetInt32(x.GetOrdinal("derrota"));
                est.savedgame = x.GetInt32(x.GetOrdinal("salvado"));

                if (est.w == 1 )
                {
                    est.nombre = "GANO";
                    
                }
                else if (est.l == 1)
                {
                    est.nombre = "PERDIO";
                }
                else if (est.savedgame == 1)
                {
                    est.nombre = "SALVO";
                }
                else
                {
                    est.nombre = "SIN DECISION";
                }
                
                est.average = this.CalculaERA(est.erun, est.ip, 9);
                _list.Add(est);
            }

            CerrarConexion();
            return _list;
        }

        public List<EstadisticasPitcheo> getInfoPitcher(int idPlayer, int idTorneo)
        {
            List<EstadisticasPitcheo> _list = new List<EstadisticasPitcheo>();
            string sql = @"SELECT SUM(pitching_statistic.w), SUM(pitching_statistic.l)
                              FROM    (   (   .game_tournament game_tournament
                                           JOIN
                                              .pgame pgame
                                           ON (game_tournament.id_game = pgame.id))
                                       JOIN
                                          .tournament tournament
                                       ON (game_tournament.id_tournament = tournament.id))
                                   JOIN
                                      .pitching_statistic pitching_statistic
                                   ON (pitching_statistic.id_game = pgame.id)
                             WHERE     (tournament.id = ?idTorneo)
                                   AND (pitching_statistic.id_player = ?idPlayer)";
            return _list;
        }


        public List<EstadisticaDefensiva> getEstadisticasDefensivasByTournament(int idPlayer)
        {
            List<EstadisticaDefensiva> _list = new List<EstadisticaDefensiva>();
            string sql = @"SELECT player.id as idPlayer,
                               team.name as equipo,
                               category.description as categoria,
                               tournament.name as torneo,
                                tournament.id as idTorneo,
                               league.name as liga,
                               SUM(defensive_statistic.inning) as inning,
                               SUM(defensive_statistic.outs) as outs,
                               SUM(defensive_statistic.a) as a,
                               SUM(defensive_statistic.e) as error,
                               SUM(defensive_statistic.dp) as dobleplay,
                               SUM(defensive_statistic.pb) as passball,
                               dposition.description as position
                          FROM    (   (   (   (   (   (   (   (   (   (   (   tournament_category tournament_category
                                                                           INNER JOIN
                                                                              tournament tournament
                                                                           ON (tournament_category.id_tournament =
                                                                                  tournament.id))
                                                                       INNER JOIN
                                                                          category category
                                                                       ON (tournament_category.id_category =
                                                                              category.id))
                                                                   INNER JOIN
                                                                      category_cgroup category_cgroup
                                                                   ON (category_cgroup.id_categoria =
                                                                          category.id))
                                                               INNER JOIN
                                                                  cgroup cgroup
                                                               ON (category_cgroup.id_group =
                                                                      cgroup.id))
                                                           INNER JOIN
                                                              cgroup_team cgroup_team
                                                           ON (cgroup_team.id_group = cgroup.id))
                                                       INNER JOIN
                                                          team team
                                                       ON (cgroup_team.id_team = team.id))
                                                   INNER JOIN
                                                      player_team player_team
                                                   ON (player_team.id_team = team.id))
                                               INNER JOIN
                                                  player player
                                               ON (player_team.id_player = player.id))
                                           INNER JOIN
                                              defensive_statistic defensive_statistic
                                           ON (defensive_statistic.id_player = player.id))
                                       INNER JOIN
                                          dposition dposition
                                       ON (defensive_statistic.id_position = dposition.id))
                                   INNER JOIN
                                      league_tournament league_tournament
                                   ON (league_tournament.id_tournament = tournament.id))
                               INNER JOIN
                                  league league
                               ON (league_tournament.id_league = league.id)
                         WHERE (player.id = ?idplayer )
                        GROUP BY player.id,
                                 dposition.description";
            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);
            comando.Parameters.Add(new MySqlParameter("?idplayer", MySqlDbType.Int32));
            comando.Parameters["?idplayer"].Value = idPlayer;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticaDefensiva estadistica = new EstadisticaDefensiva();
                estadistica.a = x.GetInt32(x.GetOrdinal("a"));
                estadistica.idPlayer = x.GetInt32(x.GetOrdinal("idPlayer"));
                estadistica.ip = CalculodeInning( x.GetInt32(x.GetOrdinal("inning")));
                estadistica.outs = x.GetInt32(x.GetOrdinal("outs"));
                estadistica.e = x.GetInt32(x.GetOrdinal("error"));
                estadistica.dp = x.GetInt32(x.GetOrdinal("dobleplay"));
                estadistica.pb = x.GetInt32(x.GetOrdinal("passball"));
                estadistica.id = x.GetInt32(x.GetOrdinal("idTorneo"));
                estadistica.positionString = x.GetString(x.GetOrdinal("position"));
                estadistica.PlayerName = x.GetString(x.GetOrdinal("torneo")) + " / " + x.GetString(x.GetOrdinal("equipo"));

                _list.Add(estadistica);
            }

            CerrarConexion();
            return _list;

        }

        public List<EstadisticaOfensiva> getEstadisticasOfensivasByTournament(int idPlayer)
        {
            List<EstadisticaOfensiva> _list = new List<EstadisticaOfensiva>();
            string sql = @"SELECT player.id as idplayer,
                               COUNT(offensive_statistic.id_player) as juego, 
                               SUM(offensive_statistic.h) as hits,
                               SUM(offensive_statistic.h2) as doble,
                               SUM(offensive_statistic.h3) as triple,
                               SUM(offensive_statistic.hr) as homerun,
                               SUM(offensive_statistic.bb) as boleto,
                               SUM(offensive_statistic.so) as ponche,
                               SUM(offensive_statistic.ab) as turno,
                               SUM(offensive_statistic.rbi) as rbi ,
                               SUM(offensive_statistic.sb) as robadas,
                               SUM(offensive_statistic.run) as anotadas,
                               SUM(offensive_statistic.ibb) as boletoi,
                               team.name as equipo,
                               tournament.name as torneo,
                                tournament.id as idTorneo,
                               league.name as liga
                          FROM    (   (   (   (   (   (   (   (   (   (   category_cgroup category_cgroup
                                                                       INNER JOIN
                                                                          category category
                                                                       ON (category_cgroup.id_categoria =
                                                                              category.id))
                                                                   INNER JOIN
                                                                      cgroup cgroup
                                                                   ON (category_cgroup.id_group =
                                                                          cgroup.id))
                                                               INNER JOIN
                                                                  cgroup_team cgroup_team
                                                               ON (cgroup_team.id_group = cgroup.id))
                                                           INNER JOIN
                                                              team team
                                                           ON (cgroup_team.id_team = team.id))
                                                       INNER JOIN
                                                          player_team player_team
                                                       ON (player_team.id_team = team.id))
                                                   INNER JOIN
                                                      player player
                                                   ON (player_team.id_player = player.id))
                                               INNER JOIN
                                                  offensive_statistic offensive_statistic
                                               ON (offensive_statistic.id_player = player.id))
                                           INNER JOIN
                                              tournament_category tournament_category
                                           ON (tournament_category.id_category = category.id))
                                       INNER JOIN
                                          tournament tournament
                                       ON (tournament_category.id_tournament = tournament.id))
                                   INNER JOIN
                                      league_tournament league_tournament
                                   ON (league_tournament.id_tournament = tournament.id))
                               INNER JOIN
                                  league league
                               ON (league_tournament.id_league = league.id)
                         WHERE (player.id = ?idplayer)
                        GROUP BY player.id";
            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);
            comando.Parameters.Add(new MySqlParameter("?idplayer", MySqlDbType.Int32));
            comando.Parameters["?idplayer"].Value = idPlayer;


            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticaOfensiva estadistica = new EstadisticaOfensiva();
                estadistica.id_player = x.GetInt32(x.GetOrdinal("idplayer"));
                estadistica.id = x.GetInt32(x.GetOrdinal("juego"));
                estadistica.h = x.GetInt32(x.GetOrdinal("hits"));
                estadistica.h2 = x.GetInt32(x.GetOrdinal("doble"));
                estadistica.h3 = x.GetInt32(x.GetOrdinal("triple"));
                estadistica.hr = x.GetInt32(x.GetOrdinal("homerun"));
                estadistica.bb = x.GetInt32(x.GetOrdinal("boleto"));
                estadistica.so = x.GetInt32(x.GetOrdinal("ponche"));
                estadistica.ab = x.GetInt32(x.GetOrdinal("turno"));
                estadistica.rbi = x.GetInt32(x.GetOrdinal("rbi"));
                estadistica.run = x.GetInt32(x.GetOrdinal("anotadas"));
                estadistica.sb = x.GetInt32(x.GetOrdinal("robadas"));
                estadistica.ibb = x.GetInt32(x.GetOrdinal("boletoi"));
                estadistica.nombre = x.GetString(x.GetOrdinal("torneo")) + " / " + x.GetString(x.GetOrdinal("equipo"));
                estadistica.id_game = x.GetInt32(x.GetOrdinal("idTorneo"));
                estadistica.average = this.CalculaAverage(estadistica.h, estadistica.h2, estadistica.h3, estadistica.hr, estadistica.ab);
                estadistica.slugging = this.CalculaSluggin(estadistica.h, estadistica.h2, estadistica.h3, estadistica.hr, estadistica.ab);
                _list.Add(estadistica);

            }

            CerrarConexion();
            return _list;
        }

        public List<EstadisticasPitcheo> getEstadisticasPitcheoByTournament(int idPlayer)
        {
            List<EstadisticasPitcheo> _list = new List<EstadisticasPitcheo>();
            string sql = @"SELECT player.id as idPlayer,
                               team.name as equipo,
                               category.description as categoria,
                               tournament.name as torneo,
                                tournament.id as idTorneo,
                               league.name as liga,
                               SUM(pitching_statistic.w) as victorias,
                               SUM(pitching_statistic.s) as salvados,
                               SUM(pitching_statistic.l) as perdidos,
                               SUM(pitching_statistic.cg) as completos,
                               SUM(pitching_statistic.er) as carrerasl,
                               SUM(pitching_statistic.ip) as innings,
                               SUM(pitching_statistic.h) as hits,
                               SUM(pitching_statistic.h2) as dobles,
                               SUM(pitching_statistic.h3) as triples,
                               SUM(pitching_statistic.hr) as homerun,
                               SUM(pitching_statistic.bb) as boleto,
                               SUM(pitching_statistic.ibb) as boletoi,
                               SUM(pitching_statistic.so) as ponche,
                               SUM(pitching_statistic.run) as carreras,
                               pitching_statistic.id_pitchingType as tipoPitcher
                          FROM    (   (   (   (   (   (   (   (   (   (   tournament_category tournament_category
                                                                       INNER JOIN
                                                                          tournament tournament
                                                                       ON (tournament_category.id_tournament =
                                                                              tournament.id))
                                                                   INNER JOIN
                                                                      category category
                                                                   ON (tournament_category.id_category =
                                                                          category.id))
                                                               INNER JOIN
                                                                  category_cgroup category_cgroup
                                                               ON (category_cgroup.id_categoria =
                                                                      category.id))
                                                           INNER JOIN
                                                              cgroup cgroup
                                                           ON (category_cgroup.id_group = cgroup.id))
                                                       INNER JOIN
                                                          cgroup_team cgroup_team
                                                       ON (cgroup_team.id_group = cgroup.id))
                                                   INNER JOIN
                                                      team team
                                                   ON (cgroup_team.id_team = team.id))
                                               INNER JOIN
                                                  player_team player_team
                                               ON (player_team.id_team = team.id))
                                           INNER JOIN
                                              player player
                                           ON (player_team.id_player = player.id))
                                       INNER JOIN
                                          pitching_statistic pitching_statistic
                                       ON (pitching_statistic.id_player = player.id))
                                   INNER JOIN
                                      league_tournament league_tournament
                                   ON (league_tournament.id_tournament = tournament.id))
                               INNER JOIN
                                  league league
                               ON (league_tournament.id_league = league.id)
                         WHERE (player.id = ?idplayer)
                        GROUP BY player.id,
                                 pitching_statistic.id_pitchingType";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);
            comando.Parameters.Add(new MySqlParameter("?idplayer", MySqlDbType.Int32));
            comando.Parameters["?idplayer"].Value = idPlayer;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                EstadisticasPitcheo estadistica = new EstadisticasPitcheo();
                estadistica.id_player = x.GetInt32(x.GetOrdinal("idPlayer"));
                estadistica.positionString = x.GetString(x.GetOrdinal("tipoPitcher"));
                estadistica.nombre = x.GetString(x.GetOrdinal("torneo")) + " / " + x.GetString(x.GetOrdinal("equipo"));
                estadistica.w = x.GetInt32(x.GetOrdinal("victorias"));
                estadistica.savedgame = x.GetInt32(x.GetOrdinal("salvados"));
                estadistica.l = x.GetInt32(x.GetOrdinal("perdidos"));
                estadistica.cg = x.GetInt32(x.GetOrdinal("completos"));
                estadistica.erun = x.GetInt32(x.GetOrdinal("carrerasl"));
                estadistica.h = x.GetInt32(x.GetOrdinal("hits"));
                estadistica.h2 = x.GetInt32(x.GetOrdinal("dobles"));
                estadistica.h3 = x.GetInt32(x.GetOrdinal("triples"));
                estadistica.hr = x.GetInt32(x.GetOrdinal("homerun"));
                estadistica.bb = x.GetInt32(x.GetOrdinal("boleto"));
                estadistica.ibb = x.GetInt32(x.GetOrdinal("boletoi"));
                estadistica.so = x.GetInt32(x.GetOrdinal("ponche"));
                estadistica.run = x.GetInt32(x.GetOrdinal("carreras"));
                estadistica.id_game = x.GetInt32(x.GetOrdinal("idTorneo"));
                estadistica.ip = x.GetDouble(x.GetOrdinal("innings"));
                estadistica.average = this.CalculaERA(estadistica.erun, estadistica.ip, 7);
                _list.Add(estadistica);
            }

            CerrarConexion();
            return _list;

        }


        #endregion

        #region masters

        /// <summary>
        /// Dada la tabla y el campo devuelve una lista de objetos id => Valor. 
        /// Solo debe utilizarse cuando el campo es de tipo ENUM
        /// </summary>
        /// <param name="tabla">Nombre de la Tabla</param>
        /// <param name="campo">Campo de Tipo ENUM de la TABLA</param>
        /// <returns></returns>
        public List<Master> getTabla(string tabla, string campo)
        {
            List<Master> _list = new List<Master>();

            string sql = "SHOW COLUMNS FROM " + tabla + " LIKE '" + campo + "'";
            this.AbrirConexion();
            comando = new MySqlCommand(sql);
            comando.Connection = this.conexion;
            comando.CommandType = System.Data.CommandType.Text;
            MySqlDataReader x = comando.ExecuteReader();

            string campos = "";
            while (x.Read())
            {
                campos = x.GetString(x.GetOrdinal("type"));
            }

            campos = campos.Replace("enum(", "");
            campos = campos.Replace(")", "");
            campos = campos.Replace("'", "");

            int i = 1;
            foreach (var item in campos.Split(new char[]{','}))
            {
                
                Master master = new Master();
                master.id = i;
                master.descripcion = item;
                _list.Add(master);
                i++;
            }
            this.CerrarConexion();
            return _list;

        }


        public List<Master> getTabla(string tabla)
        {
            List<Master> _list = new List<Master>();

            string sql = @"SELECT id, description 
                            FROM dposition;";
            this.AbrirConexion();
            comando = new MySqlCommand(sql);
            comando.Connection = this.conexion;
            comando.CommandType = System.Data.CommandType.Text;
            MySqlDataReader x = comando.ExecuteReader();

            while (x.Read())
            {
                Master master = new Master();
                master.id = x.GetInt32(x.GetOrdinal("id"));
                master.descripcion = x.GetString("description");
                _list.Add(master);
            }

            this.CerrarConexion();
            return _list;

        }

        public List<Master> getDefensiveSortingFields()
        {
            List<Master> _list = new List<Master>();
            string[] lista = { "% de Fildeo", "Asistencias", "Errores", "Outs", "Innings", "Doble Plays", "Pass Ball" };
            for (int i = 0; i < lista.Length- 1; i++)
            {
                Master master = new Master() { id = i, descripcion = lista[i] };
                _list.Add(master);
            }

            return _list;
        
        }

        public List<Master> getOffensiveSortingFields()
        {
            List<Master> _list = new List<Master>();
            string[] lista = { "Average", "HomeRuns", "Impulsadas", "Carreras Anotadas", "Hits", "Dobles", "Triples", "Boletos", "Boletos Intencionales", "Ponches", "Bases Robadas", "Slugging" };
            for (int i = 0; i < lista.Length - 1; i++)
            {
                Master master = new Master() { id = i, descripcion = lista[i] };
                _list.Add(master);
            }

            return _list;

        }

        public List<Master> getPitchingSortingFields()
        {
            List<Master> _list = new List<Master>();
            string[] lista = {"Victorias", "ERA","Ponches", "Derrotas", "Salvados", "Juegos Completo", "Carreras", "Carreras Limpias", "Innings", "Hits", "Dobles", "Triples", "Home Runs", "Boletos", "Boletos Intencionales" };
            for (int i = 0; i < lista.Length - 1; i++)
            {
                Master master = new Master() { id = i, descripcion = lista[i] };
                _list.Add(master);
            }

            return _list;

        }








        #endregion

        #region juegos

        /// <summary>
        /// Detalles de un juego
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Juego getJuego(int id)
        {
            Juego _juego = new Juego();
            string sql = @"SELECT result.end_type as final, pgame.id as idjuego,
                                   pgame.scheduled as fecha,
                                   pgame.type as tipo,
                                   stadium.name as nombrestadio,
                                   stadium.description
                              FROM    (   pgame pgame
                                       INNER JOIN
                                          stadium stadium
                                       ON (pgame.id_stadium = stadium.id))
                                   INNER JOIN
                                      result result
                                   ON (result.id_game = pgame.id)
                             WHERE (pgame.id = ?idjuego);";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idjuego", MySqlDbType.Int32));
            comando.Parameters["?idjuego"].Value = id;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                _juego.id = id;
                _juego.scheduled = x.GetDateTime(x.GetOrdinal("fecha"));
                //_juego.team_hc = x.GetString(x.GetOrdinal("tipo"));

            }


            CerrarConexion();
            return _juego;
        }

        public List<Juego> getNextGamesByTournament(int idtorneo)
        {
            List<Juego> _list = new List<Juego>();
            string sql = @"SELECT pgame.id_team_hc as idTeamHC,
                               pgame.id_team_visitor as idTeamVisitor,
                               pgame.scheduled as Fecha,
                               pgame.type as tipoJuego, 
                               pgame.id as idJuego,
                              (select t.name from team t where t.id = pgame.id_team_visitor) as visitante,
                              (select t.name from team t where t.id = pgame.id_team_hc) as homeclub
                          FROM    (   game_tournament game_tournament
                                   INNER JOIN
                                      pgame pgame
                                   ON (game_tournament.id_game = pgame.id))
                               INNER JOIN
                                  tournament tournament
                               ON (game_tournament.id_tournament = tournament.id)
                         WHERE (tournament.id = ?idTorneo)
                         AND (pgame.scheduled BETWEEN CURDATE() AND DATE_ADD(CURDATE(), interval 60 DAY))
                         ORDER BY pgame.id asc
                        LIMIT 10
                        ";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idTorneo", MySqlDbType.Int32));
            comando.Parameters["?idTorneo"].Value = idtorneo;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Juego juego = new Juego();
                juego.id_team_hc = x.GetInt32(x.GetOrdinal("idTeamHC"));
                juego.id_team_visitor = x.GetInt32(x.GetOrdinal("idTeamVisitor"));

                juego.team_hc = x.GetString(x.GetOrdinal("homeclub"));
                juego.team_visitor = x.GetString(x.GetOrdinal("visitante"));

                juego.scheduled = x.GetDateTime(x.GetOrdinal("Fecha"));
                juego.id = x.GetInt32(x.GetOrdinal("idJuego"));
                juego.type = x.GetString(x.GetOrdinal("tipoJuego"));


                _list.Add(juego);
            }

            CerrarConexion();
            return _list;
        }

        public List<Juego> getNextGamesByTeam(int idEquipo)
        {
            List<Juego> _list = new List<Juego>();
            string sql = @"SELECT pgame.id_team_hc as idTeamHC, pgame.id as idJuego, 
                                   pgame.id_team_visitor as idTeamV ,
                                   pgame.scheduled as fecha,
                                   pgame.type as tipoJuego ,
                                   pgame.id_stadium as idStadium,
                                  (select t.name from team t where t.id = pgame.id_team_visitor) as TeamV,
                                  (select t.name from team t where t.id = pgame.id_team_hc) as TeamHC
                              FROM    (   (   (   (   (   (   (   category_cgroup category_cgroup
                                                               INNER JOIN
                                                                  cgroup cgroup
                                                               ON (category_cgroup.id_group = cgroup.id))
                                                           INNER JOIN
                                                              category category
                                                           ON (category_cgroup.id_categoria = category.id))
                                                       INNER JOIN
                                                          tournament_category tournament_category
                                                       ON (tournament_category.id_category = category.id))
                                                   INNER JOIN
                                                      tournament tournament
                                                   ON (tournament_category.id_tournament = tournament.id))
                                               INNER JOIN
                                                  game_tournament game_tournament
                                               ON (game_tournament.id_tournament = tournament.id))
                                           INNER JOIN
                                              pgame pgame
                                           ON (game_tournament.id_game = pgame.id))
                                       INNER JOIN
                                          cgroup_team cgroup_team
                                       ON (cgroup_team.id_group = cgroup.id))
                                   INNER JOIN
                                      team team
                                   ON (cgroup_team.id_team = team.id)
                             WHERE ((pgame.id_team_hc = ?idEquipo) OR (pgame.id_team_visitor = ?idEquipo))                               
                             AND (pgame.scheduled BETWEEN CURDATE() AND DATE_ADD(CURDATE(), interval 60 DAY))
                            GROUP BY pgame.id
                             ORDER BY pgame.id desc
                            LIMIT 10
                            ";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idEquipo", MySqlDbType.Int32));
            comando.Parameters["?idEquipo"].Value = idEquipo;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Juego juego = new Juego();
                juego.id = x.GetInt32(x.GetOrdinal("idJuego"));
                juego.id_stadium = x.GetInt32(x.GetOrdinal("idStadium"));
                juego.id_team_hc = x.GetInt32(x.GetOrdinal("idTeamHC"));
                juego.id_team_visitor = x.GetInt32(x.GetOrdinal("idTeamV"));
                juego.scheduled = x.GetDateTime(x.GetOrdinal("fecha"));
                juego.team_hc = x.GetString(x.GetOrdinal("TeamHC"));
                juego.team_visitor = x.GetString(x.GetOrdinal("TeamV"));
                juego.type = x.GetString(x.GetOrdinal("tipoJuego"));

                _list.Add(juego);
            }

            CerrarConexion();
            return _list;
        }


        public List<Juego> getLatestResult(int idTorneo)
        {
            List<Juego> _juego = new List<Juego>();

            string sql = @"SELECT pgame.id_team_hc AS idTeamHC,
                                   pgame.id_team_visitor AS idTeamV,
                                   pgame.scheduled AS fecha,
                                   result.runs_hc AS runHC,
                                   result.runs_visitor AS runV,
                                   result.hit_hc AS hitHC,
                                   result.hit_visitor AS hitV,
                                   result.error_hc AS eHC,
                                   result.error_visitor AS eV,
                                   result.end_type AS finalJuego,
                                   result.id_game AS idJuego,
                                   pgame.type AS tipoJuego,
                                   pgame.id_stadium AS idStadium, 
                                   (SELECT name FROM team where id = pgame.id_team_hc) as TeamHC,
                                   (SELECT name FROM team where id = pgame.id_team_visitor) as TeamV
                              FROM    (   (   game_tournament game_tournament
                                           INNER JOIN
                                              pgame pgame
                                           ON (game_tournament.id_game = pgame.id))
                                       INNER JOIN
                                          tournament tournament
                                       ON (game_tournament.id_tournament = tournament.id))
                                   INNER JOIN
                                      result result
                                   ON (result.id_game = pgame.id)
                             WHERE (tournament.id = ?idTorneo)
                             ORDER BY fecha DESC
                             limit 10";
            AbrirConexion();
            MySqlCommand comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idTorneo", MySqlDbType.Int32));
            comando.Parameters["?idTorneo"].Value = idTorneo;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
	        {
        	    Juego juego = new Juego();
                juego.end_type = x.GetString(x.GetOrdinal("finalJuego"));
                juego.error_hc = x.GetInt32(x.GetOrdinal("eHC"));
                juego.error_visitor = x.GetInt32(x.GetOrdinal("eV"));
                juego.hit_hc = x.GetInt32(x.GetOrdinal("hitHC"));
                juego.hit_visitor = x.GetInt32(x.GetOrdinal("hitV"));
                juego.id = x.GetInt32(x.GetOrdinal("idJuego"));
                juego.id_stadium = x.GetInt32(x.GetOrdinal("idStadium"));
                juego.id_team_hc = x.GetInt32(x.GetOrdinal("idTeamHC"));
                juego.id_team_visitor = x.GetInt32(x.GetOrdinal("idTeamV"));
                juego.runs_hc = x.GetInt32(x.GetOrdinal("runHC"));
                juego.runs_visitor = x.GetInt32(x.GetOrdinal("runV"));
                juego.scheduled = x.GetDateTime(x.GetOrdinal("fecha"));
                juego.team_hc = x.GetString(x.GetOrdinal("TeamHC"));
                juego.team_visitor = x.GetString(x.GetOrdinal("TeamV"));
                juego.type = x.GetString(x.GetOrdinal("tipoJuego"));
                _juego.Add(juego);
	        }

            CerrarConexion();
            return _juego;
    
        }

        public List<Juego> getLatestResultByTeam(int idEquipo)
        {
            List<Juego> _list = new List<Juego>();
            string sql = @"SELECT result.runs_hc AS runHC,
                                   result.runs_visitor AS runV,
                                   result.hit_hc AS hitHC,
                                   result.hit_visitor AS hitV,
                                   result.error_hc AS eHC,
                                   result.error_visitor AS eV,
                                   result.end_type AS finalJuego,
                                   pgame.id_team_hc AS idTeamHC,
                                   pgame.scheduled AS fecha,
                                   pgame.id_team_visitor AS idTeamV,
                                   pgame.type AS tipoJuego,
                                   pgame.id_stadium AS idStadium,
                                   pgame.id AS idJuego,
                                   (SELECT name FROM team where id = pgame.id_team_hc) as TeamHC,
                                   (SELECT name FROM team where id = pgame.id_team_visitor) as TeamV
                              FROM    (   pgame pgame
                                       INNER JOIN
                                          stadium stadium
                                       ON (pgame.id_stadium = stadium.id))
                                   INNER JOIN
                                      result result
                                   ON (result.id_game = pgame.id)
                             WHERE (pgame.id_team_hc = ?idEquipo)
                             OR (pgame.id_team_visitor = ?idEquipo)
                            LIMIT 5";
            AbrirConexion();
            MySqlCommand comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idEquipo", MySqlDbType.Int32));
            comando.Parameters["?idEquipo"].Value = idEquipo;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Juego juego = new Juego();
                juego.end_type = x.GetString(x.GetOrdinal("finalJuego"));
                juego.error_hc = x.GetInt32(x.GetOrdinal("eHC"));
                juego.error_visitor = x.GetInt32(x.GetOrdinal("eV"));
                juego.hit_hc = x.GetInt32(x.GetOrdinal("hitHC"));
                juego.hit_visitor = x.GetInt32(x.GetOrdinal("hitV"));
                juego.id = x.GetInt32(x.GetOrdinal("idJuego"));
                juego.id_stadium = x.GetInt32(x.GetOrdinal("idStadium"));
                juego.id_team_hc = x.GetInt32(x.GetOrdinal("idTeamHC"));
                juego.id_team_visitor = x.GetInt32(x.GetOrdinal("idTeamV"));
                juego.runs_hc = x.GetInt32(x.GetOrdinal("runHC"));
                juego.runs_visitor = x.GetInt32(x.GetOrdinal("runV"));
                juego.scheduled = x.GetDateTime(x.GetOrdinal("fecha"));
                juego.team_hc = x.GetString(x.GetOrdinal("TeamHC"));
                juego.team_visitor = x.GetString(x.GetOrdinal("TeamV"));
                juego.type = x.GetString(x.GetOrdinal("tipoJuego"));
                _list.Add(juego);
            }

            CerrarConexion();
            return _list;
            
        }
        

        public List<Juego> getGameResult(int id)
        {
            List<Juego> _list = new List<Juego>();
            string sql = @" SELECT pgame.id_team_hc as idTeamHC,
                               pgame.id_team_visitor as idTeamV,
                               pgame.scheduled as fecha,
                               pgame.type as tipoJuego,
                               stadium.id as idEstadio ,
                               result.runs_hc as runhc,
                               result.runs_visitor as runv,
                               result.hit_hc as hithc,
                               result.hit_visitor as hitv,
                               result.error_hc as errorhc,
                               result.error_visitor as errorv,
                               result.end_type as tipoFinal, 
                               (select t.name from team t where t.id = pgame.id_team_visitor) as visitante,
                               (select t.name from team t where t.id = pgame.id_team_hc) as homeclub
                          FROM    (   pgame pgame
                                   INNER JOIN
                                      stadium stadium
                                   ON (pgame.id_stadium = stadium.id))
                               INNER JOIN
                                  result result
                               ON (result.id_game = pgame.id)
                         WHERE (pgame.id = ?idjuego)";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idjuego", MySqlDbType.Int32));
            comando.Parameters["?idjuego"].Value = id;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Juego juego = new Juego();
                juego.id_team_hc = x.GetInt32(x.GetOrdinal("idTeamHC"));
                juego.id_team_visitor = x.GetInt32(x.GetOrdinal("idTeamV"));
                juego.scheduled = x.GetDateTime(x.GetOrdinal("fecha"));
                juego.type = x.GetString(x.GetOrdinal("tipoJuego"));
                juego.id_stadium = x.GetInt32(x.GetOrdinal("idEstadio"));
                juego.runs_hc = x.GetInt32(x.GetOrdinal("runhc"));
                juego.runs_visitor = x.GetInt32(x.GetOrdinal("runv"));
                juego.hit_hc = x.GetInt32(x.GetOrdinal("hithc"));
                juego.hit_visitor = x.GetInt32(x.GetOrdinal("hitv"));
                juego.error_hc = x.GetInt32(x.GetOrdinal("errorhc"));
                juego.error_visitor = x.GetInt32(x.GetOrdinal("errorv"));
                juego.end_type = x.GetString(x.GetOrdinal("tipoFinal"));
                juego.id = id;
                juego.team_hc = x.GetString(x.GetOrdinal("homeclub"));
                juego.team_visitor = x.GetString(x.GetOrdinal("visitante"));
                _list.Add(juego);
            }


            CerrarConexion();
            return _list;
        
        }

        public List<Juego> getGamesNoPlayedByAnotador()
        {
            List<Juego> _list = new List<Juego>();
            string sql = @"SELECT pgame.id_team_hc as idTeamHC,
                                   pgame.id_team_visitor as idTeamV,
                                   pgame.type as tipoJuego,
                                   pgame.scheduled as fecha,
                                   pgame.id_stadium as idEstadio,
                                   pgame.id as id,
                                  (select t.name from team t where t.id = pgame.id_team_visitor) as visitante,
                                  (select t.name from team t where t.id = pgame.id_team_hc) as homeclub
                              FROM    result result, pgame pgame
                              WHERE (pgame.id not in (select r.id_game from result r)) and pgame.scheduled between DATE_ADD(curdate(), INTERVAL -31 DAY) and curdate()";

            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);

//            comando.Parameters.Add(new MySqlParameter("?idjuego", MySqlDbType.Int32));
//            comando.Parameters["?idjuego"].Value = id;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Juego juego = new Juego();
                juego.id_team_hc = x.GetInt32(x.GetOrdinal("idTeamHC"));
                juego.id_team_visitor = x.GetInt32(x.GetOrdinal("idTeamV"));
                juego.scheduled = x.GetDateTime(x.GetOrdinal("fecha"));
                juego.type = x.GetString(x.GetOrdinal("tipoJuego"));
                juego.id_stadium = x.GetInt32(x.GetOrdinal("idEstadio"));
                juego.id = x.GetInt32(x.GetOrdinal("id"));
                juego.team_hc = x.GetString(x.GetOrdinal("homeclub"));
                juego.team_visitor = x.GetString(x.GetOrdinal("visitante"));
                _list.Add(juego);
            }

            CerrarConexion();
            return _list;
        
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="idGame">idJuego a consultar</param>
        /// <param name="idTeam">1 si es visitante, 0 si es HomeClub</param>
        /// <returns></returns>
        public List<Master> getScoreByTeamByInning(int idGame, int idTeam)
        {
            List<Master> _list = new List<Master>();
            string sql = "";

            if (idTeam == 0)
            {
                sql = @"select run_hc as run, inning from result_details where id_game = ?idJuego 
                        order by inning asc;";
                
            }
            else
            {
                sql = @"select run_visitor as run, inning from result_details where id_game = ?idJuego 
                        order by inning asc;";

            }
            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);
            comando.Parameters.Add(new MySqlParameter("?idJuego", MySqlDbType.Int32));
            comando.Parameters["?idJuego"].Value = idGame;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Master master = new Master();
                master.descripcion = x.GetString(x.GetOrdinal("run"));
                if (master.descripcion == "-1")
                {
                    master.descripcion = "X";
                }
                _list.Add(master);
            }
            

            CerrarConexion();
            return _list;
        }

        

        #endregion

        #region estadisticas
        public EstadisticaOfensiva getEstadisticaByGame(int idjuego, int idplayer)
        {
            EstadisticaOfensiva _estadistica = new EstadisticaOfensiva();

            return _estadistica;
        }

        public EstadisticasPitcheo getPlayerEstadisticaPitcheoByGame(int idjuego, int idplayer)
        {
            EstadisticasPitcheo _est = new EstadisticasPitcheo();

            return _est;
        }

        public EstadisticaDefensiva getPlayerEstadisticasDefensivasByGame(int idjuego, int idplayer)
        {
            EstadisticaDefensiva _est = new EstadisticaDefensiva();

            return _est;
        }

        #endregion

        #region Estadios
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Estadio> getEstadiumByLeague(int idLeague)
        {
            List<Estadio> _stadium = new List<Estadio>();
            string sql = @"SELECT stadium.id,
                                   stadium.name,
                                   stadium.description,
                                   stadium.picture,
                                   stadium.address,
                                   stadium.longitud,
                                   stadium.latitud
                              FROM    (   (   (   stadium_tournament stadium_tournament
                                               JOIN
                                                  tournament tournament
                                               ON (stadium_tournament.id_tournament = tournament.id))
                                           JOIN
                                              stadium stadium
                                           ON (stadium_tournament.id_stadium = stadium.id))
                                       JOIN
                                          league_tournament league_tournament
                                       ON (league_tournament.id_tournament = tournament.id))
                                   JOIN
                                      league league
                                   ON (league_tournament.id_league = league.id)
                             WHERE (league.id = ?idLeague)
                        ;";
            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);
            comando.Parameters.Add(new MySqlParameter("?idLeague", MySqlDbType.Int32));
            comando.Parameters["?idLeague"].Value = idLeague;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Estadio estadio = new Estadio();
                estadio.name = x.GetString(x.GetOrdinal("name"));
                estadio.description = x.GetString(x.GetOrdinal("description"));
                estadio.picture = x.GetString(x.GetOrdinal("picture"));
                estadio.direccion = x.GetString(x.GetOrdinal("address"));
                estadio.longitud = x.GetDouble(x.GetOrdinal("longitud"));
                estadio.latitud = x.GetDouble(x.GetOrdinal("latitud"));
                estadio.id = x.GetInt32(x.GetOrdinal("id"));
                _stadium.Add(estadio);

            }

            CerrarConexion();

            return _stadium;
            
        }

        public Estadio getEstadio(int id)
        {
            Estadio estadio = new Estadio();
            string sql = @"SELECT id, name, description, picture, address, longitud, latitud 
                                FROM .stadium;
                        ;";
            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);
            comando.Parameters.Add( new MySqlParameter("?idEstadio", MySqlDbType.Int32));
            comando.Parameters["?idEstadio"].Value = id;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                estadio.name = x.GetString(x.GetOrdinal("name"));
                estadio.description = x.GetString(x.GetOrdinal("description"));
                estadio.picture = x.GetString(x.GetOrdinal("picture"));
                estadio.direccion = x.GetString(x.GetOrdinal("address"));
                estadio.id = id;
            }

            CerrarConexion();
            return estadio;
        
        }

        #endregion

        public List<Master> getPitchingResult()
        {
            List<Master> _list = new List<Master>();

            foreach (var llave in Enum.GetNames(typeof(pitchingResult)))
            {
                Master x = new Master();
                x.id = (int) Enum.Parse(typeof(pitchingResult), llave);
                x.descripcion = llave;
                _list.Add(x);
            }
            CerrarConexion();
            return _list;

        }


        #region Jornada

        public List<BoxScore.Objetos.Jornada> getJornadasByTournament(int idtorneo)
        {
            List<BoxScore.Objetos.Jornada> _list = new List<BoxScore.Objetos.Jornada>();
            string sql = @"SELECT id, finicial, ffinal, id_torneo, contador 
                                FROM jornada
                                WHERE id_torneo = ?idtorneo ORDER BY contador desc";

//            string sql = @"SELECT id, finicial, ffinal, id_torneo 
//                            FROM jornada 
//                            WHERE id_torneo = ?idtorneo";
            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);
            comando.Parameters.Add(new MySqlParameter("?idtorneo", MySqlDbType.Int16));
            comando.Parameters["?idtorneo"].Value = idtorneo;

            MySqlDataReader x = comando.ExecuteReader();
            while(x.Read())
            {
                BoxScore.Objetos.Jornada j = new BoxScore.Objetos.Jornada();
                j.id = x.GetInt32(x.GetOrdinal("id"));
                j.ffinal = x.GetDateTime(x.GetOrdinal("ffinal"));
                j.finicial = x.GetDateTime(x.GetOrdinal("finicial"));
                j.counter = x.GetInt32(x.GetOrdinal("contador"));
                j.idTorneo = idtorneo;

                _list.Add(j);
            }

            CerrarConexion();
            return _list;
        }

        public List<Juego> getGamesByTorneoAndJornada(int idjornada, int idtorneo)
        {
            List<Juego> _list = new List<Juego>();

            string sql = @"SELECT pgame.id_team_hc as idteamhc,
                                    pgame.id as idjuego,
                                   pgame.id_team_visitor as idteamv,
                                   pgame.scheduled,
                                   pgame.type,
                                   pgame.id_stadium,
                                   (SELECT name FROM team where id = id_team_hc) as homeclub, 
                                   (SELECT name FROM team where id = id_team_visitor) as visitor
                               FROM    (   (   jornada jornada
                                           JOIN
                                              tournament tournament
                                           ON (jornada.id_torneo = tournament.id))
                                       JOIN
                                          pgame_jornada pgame_jornada
                                       ON (pgame_jornada.id_jornada = jornada.id))
                                   JOIN
                                      pgame pgame
                                   ON (pgame_jornada.id_pgame = pgame.id)
                             WHERE (tournament.id = ?idtorneo) AND (jornada.id = ?idjornada)";
//            string sql = @"SELECT pgame.id as idjuego,
//                                   tournament.id as idtorneo,
//                                   jornada.id as idjornada,
//                                   pgame.id_team_visitor as idteamv,
//                                   pgame.id_team_hc as idteamhc,
//                                   pgame.type as type
//                              FROM    (   (   jornada jornada
//                                           INNER JOIN
//                                              tournament tournament
//                                           ON (jornada.id_torneo = tournament.id))
//                                       INNER JOIN
//                                          game_tournament game_tournament
//                                       ON (game_tournament.id_tournament = tournament.id))
//                                   INNER JOIN
//                                      pgame pgame
//                                   ON (game_tournament.id_game = pgame.id)
//                             WHERE (tournament.id = ?idtorneo)
//                             AND (pgame.scheduled >= jornada.finicial AND pgame.scheduled <= jornada.ffinal)
//                             AND (jornada.id = ?idJornada)";

            AbrirConexion();

            comando = new MySqlCommand(sql, conexion);
            comando.Parameters.Add(new MySqlParameter("?idjornada", MySqlDbType.Int32));
            comando.Parameters.Add(new MySqlParameter("?idtorneo", MySqlDbType.Int32));

            comando.Parameters["?idjornada"].Value = idjornada;
            comando.Parameters["?idtorneo"].Value = idtorneo;

            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Juego j = new Juego();
                j.end_type = x.GetString(x.GetOrdinal("type"));
                j.id = x.GetInt32(x.GetOrdinal("idjuego"));
                j.id_team_hc = x.GetInt32(x.GetOrdinal("idteamhc"));
                j.id_team_visitor = x.GetInt32(x.GetOrdinal("idteamv"));
                j.type = x.GetString(x.GetOrdinal("type"));
                j.team_hc = x.GetString(x.GetOrdinal("homeclub"));
                j.team_visitor = x.GetString(x.GetOrdinal("visitor"));
                _list.Add(j);
            }

            CerrarConexion();
            return _list;
        }

        #endregion

        #region Publicidad

        public List<Publicidad> getPublicidadSitio (int idOwnen)
        {
            List<Publicidad> _list = new List<Publicidad>();
            string sql = @"SELECT  tpublicidad.id,
                                   tpublicidad.imageUrl,
                                   tpublicidad.navigateUrl,
                                   tpublicidad.AlternateText,
                                   tpublicidad.priority as prioridad,
                                   tpublicidad.ancho as ancho,
                                   tpublicidad.alto as alto,
                                   tipopublicidad.id as tipo
                            FROM   
                                   tpublicidad tpublicidad
                            INNER JOIN
                                   tipopublicidad tipopublicidad
                            ON (tpublicidad.idTipo = tipopublicidad.id)
                            WHERE (tipopublicidad.id = ?idtipo)
                            AND estado = 1";

            AbrirConexion();
            comando = new MySqlCommand(sql, conexion);

            comando.Parameters.Add(new MySqlParameter("?idtipo", MySqlDbType.Int32));
            comando.Parameters["?idtipo"].Value = idOwnen;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                Publicidad p = new Publicidad();
                p.AlternateText = x.GetString(x.GetOrdinal("AlternateText"));
                p.Height = x.GetInt32(x.GetOrdinal("alto"));
                p.ancho = x.GetInt32(x.GetOrdinal("ancho"));
                p.idowner = x.GetInt32(x.GetOrdinal("tipo"));
                p.imageUrl = x.GetString(x.GetOrdinal("imageUrl"));
                p.navigateUrl = x.GetString(x.GetOrdinal("navigateUrl"));
                p.prioridad = x.GetInt32(x.GetOrdinal("prioridad"));
                _list.Add(p);
                
            }

            CerrarConexion();
            return _list;
        }



        #endregion

        #region Calculos

        private double CalculaAverage(int hits, int doble, int triple, int hr, int turnos)
        {
            double _result = 0.0;
            if (turnos == 0)
            {
                _result = 0.0;   
            }
            else
                _result =  ((double)(hits + doble + triple + hr)) / turnos;
            return _result * 1000;
        }

        private double CalculaSluggin(int hits, int doble, int triple, int hr, int turnos )
        {
            double _return = 0.0;
            if (turnos == 0)
            {
                _return = 0.0;
            }
            else
                _return = ((double)(hits + doble * 2 + triple * 3 + hr * 4) / turnos);
            return _return;
        
        }

        private double CalculaERA(int carreralimpia, double innings, int _innings)
        {
            if (innings == 0)
            {
                return 0.0;
            }
            return ((double)(carreralimpia / CalculodeInning(innings))) * _innings;
        }

        private double CalculodeInning(double innings)
        {
            double x;
            x = Math.Floor(innings) + ((innings - Math.Floor(innings)) * 3);
            return x;
        
        }

        private double CalculodePorcentajeFildeo(int outs, int errores, int asistencia)
        {
            double result = (((double)(outs + asistencia)) / ((outs + asistencia + errores)));
            return result;
        }


        #endregion


        #region notificaciones

        public Notification getNotificacionById(int id)
        {
            Notification notificacion = new Notification();
            string sql = @"SELECT notifications.description,
                                   notifications.title,
                                   notifications.status,
                                   notifications.id,
                                   notifications.idOwner,
                                   notifications.publishDate,
                                   notifications.publishEndDate
                              FROM notifications notifications
                             WHERE (notifications.id = :id)";

            this.AbrirConexion();
            comando = new MySqlCommand(sql);
            comando.Connection = this.conexion;
            comando.CommandType = System.Data.CommandType.Text;
            MySqlDataReader x = comando.ExecuteReader();
            while (x.Read())
            {
                notificacion.descripcion = x.GetString(x.GetOrdinal(""));
                notificacion.endPublishDate = x.GetDateTime(x.GetOrdinal(""));
                notificacion.id = id;
                notificacion.idOwner = x.GetInt32(x.GetOrdinal(""));
                notificacion.publishDate = x.GetDateTime(x.GetOrdinal(""));
                notificacion.status = x.GetInt32(x.GetOrdinal(""));
                notificacion.title = x.GetString(x.GetOrdinal(""));

            }

            
            this.CerrarConexion();
            return notificacion;
        
        }
        


        #endregion

    }
}
